Download ER Model -Introduction to Database Systems - Exams and more Exams Introduction to Database Management Systems in PDF only on Docsity!
UNIVERSITY OF CALIFORNIA
Department of EECS, Computer Science Division CS186 Midterm I Oct 5, 1999
NOTE: THIS IS THE EXAM THAT WAS GIVEN IN THE FALL
1999 SEMESTER. IT IS PROVIDED FOR YOUR STUDYING
PURPOSES --- WE EXPECT THAT OUR EXAM WILL BE
SIMILAR IN STYLE, BUT WE MAKE NO GUARANTEES IN
THAT REGARD. USE THIS SIMPLY A STUDY AID.
Midterm Exam: Introduction to Database Systems
This exam has five problems. Each problem is made up of multiple questions. You should read through the exam quickly and plan your time-management accordingly. Before beginning to answer a question, be sure to read it carefully and to answer all parts of every question!
REFERENCE DATABASE. This is the Reference Database referred to in this exam.
There are four tables. SALESPERSON contains the names, ids, regions & quotas for the salespeople. Ids are unique, names are not, and there is only one salesperson per region. PRODUCTS contains the product names, product ids, and prices for the products. The product ids are unique.
SALESPERSON Sname Sid Region Quota
Frances 25 TX $
Bob 31 CA $
Frances 74 MA $ Mary 89 FL $
CUSTOMERS contains the customer names, customer ids, and regions for the customers (customer ids are unique), and ORDERS contains the customer id, the product id, and the product ordered per customer.
CUSTOMERS
Cname Cid Region Bob 1 TX Harry 2 TX Lin 3 MA Martha 4 FL Lin 5 FL Leyla 6 CA
ORDERS
Cid Pid Quantity 1 152 1 2 152 1 4 831 1 4 131 1 5 255 1 6 831 1
PRODUCTS
Pname Pid Pprice disks 131 $ pcs 152 $ macs 831 $ printers 255 $ paper 221 $
Section You Usually Attend ______________________________Login_________________________
1. Relational Model and ER Model (20 points). a. (2 points) Using the Reference Database as an example, give an example of data independence contrasting the use of a file system to store the information in the SALESPERSON table with the use of a relational database to store the same information.
b. (2 points) What is the cardinality of the table SALESPERSON in the Reference Database on page 1? What is the degree of the table SALESPERSON?
c. (4 points). Name the primary keys of each of the four tables in the Reference Database on Page 1.
d. (4 points) For each table in the Reference Database on Page 1, determine whether or not it has a foreign key, and if so, list what it is and what it references.
e. (8 points) Modify the following ER diagram to represent the Reference Database on Page 1, including the salesperson entities and relationships.
Quantity
Region
ORDERS
Cname
me
CUSTOMERS
Cid
Pprice
me pid
Pname
PRODUCTS
- Indices [20 points]
b. [5 points] In the Reference Database on Page 1, name one advantage of building a clustered index on the Sname field of the SALESPERSON table. Name one disadvantage. Under what circumstances would a nonclustered index on the Sname field be the best choice?
b. [5 points] In the PRODUCTS table in the reference database on page 1, assume that there are actually many more records in the PRODUCTS table than are listed in the table shown – assume that there are P pages with R records per page. Given an Extendible Hashing index on the Pid attribute, what is the number of I/Os required (worst case) to fetch all Pids between 131and 150, including 131 & 150? You should assume that bucket pages are internally organized like heap file pages. You may assume any alternative you wish for storing data entries in the index, and for buffering data in the system, but you must state the alternative and the buffering assumptions, and present the correct analysis for those assumptions.
c. [5 points] In the reference database on Page 1, assume that the CUSTOMERS records are much larger than shown in the table on the page – in fact, each record fits in exactly one page, so the CUSTOMERS table is 5 pages long.. Draw a B+ tree index on Cname, assuming that d = 2, using any of the alternatives discussed in text, but explain which alternative you are using.
d. [5 points] The DBA for the reference database on Page 1 has created a sparse index on the PRODUCTS table (again, assuming that the PRODUCTS table has many more records than are listed on Page 1). The sparse index is on Pname. Explain in terms of accesses to the table PRODUCTS the advantages of a sparse index on Pname. Explain the disadvantages.
4. Relational Algebra (16 points)
A. (10 points; 2 points each) Show the rows resulting from the following queries, using the reference database.
1. σ Cname = “Lin” (CUSTOMERS)
2. πCname, Pid(CUSTOMERS CUSTOMERS.Cid = ORDERS.Cid ORDERS)
3. πSname ((SALESPERSON SALESPERSON.Region = CUSTOMERS.Region CUSTOMERS) CUSTOMERS.Cid = ORDERS.Cid
(σ Pname = “Mac” (PRODUCTS PRODUCTS.Pid = ORDERS.Pid ORDERS))
4. π sname SALESPERSON π Sname CUSTOMERS
5. π sname SALESPERSON π Sname CUSTOMERS
B. (6 points; 2 points each) State the relational algebra expression to find the following:
1. The names of the customers who live in the region TX.
2. The customer id’s of customers who have ordered a pc.
3. The Sids of the Salespeople who have sold a printer.
