240 likes | 441 Views
Principles of Database Systems. Exercises. Yunsheng Liu. Exercise 1. Please give at least four main differences between a DBMS and a file system Explain the difference between subschema, conceptual schema and physical schema.
E N D
Principles of Database Systems Exercises Yunsheng Liu
Exercise 1 • Please give at least four main differences between a DBMS and a file system • Explain the difference between subschema, conceptual schema and physical schema. • What are the logical and physical data independence? How are they related to the different layer schemas? • Explain the major components of a DBS • Illustrate the relations and differences between type and occurrence. YSLiu-DBS-Exercise Oct. 23, 2008
Exercise 2 • Explain the basic properties of the three classical data models, respectively, and the main differences among them. • Explain Entity, Relationship, Attribute and Key. And what are the relations between them • Use the E-R diagram to describe the data connected with an enterprise, including depts, projects, products and suppliers with their necessary attributes and indicating the keys. YSLiu-DBS-Exercise Oct. 16, 2008
Exercise 2-1 • Please design an E-R diagram to represent the courses schedules in a university ( what kinds of information? ) • Please give an example of a complete E-R diagram and translate it into hierachical, network and relational schemas, respectivelt • Explain the relations and differences between -join, equijoin and natural join. YSLiu-DBS-Exercise Nov. 3, 2007
Exercise 2-2 • Give the following relatiom schemas: Empl (E#, Ename, Age, Address) Works (E#, Dname, Term, Salary) Dept (Dname, Office-building, Office-Phone) Manager (Mid, Dname, Phone) Please answer the following questions: (1). Give the primary key of each relation (2). Draw the E-R diagram corresponding to these relationsa YSLiu-DBS-Exercise
Exercise 2-2 (3). Express the following queries with relational algebra: • Find the phone number of Planning Dept. • Find the the names and ages of employees who are 20~35 years old. • Find the the names and addresses of employees of Development Dept. • Find the names of the department managers whose office are in building B-XYZ • Find the phone number of the department within which 张三 works. YSLiu-DBS-Exercise
Exercise 2-3 • What kinds of relational operations form a relational algebra? Why is it? • Let R, S be the relations shown as follows: R: A B S: B C a b b c d e c a b c e dCompute: (1)R∪S: (2)R∩S; (3)R-S; (4)R⋈ S; (5)A(R); (6) A=C(R×S); (6) R⋈B<CS, where <represent alphabetic YSLiu-DBS-Exercise
R S S’ A B C a b c b a d c a d B C D b c d b c a a d c c d a A’ B’ C’ b a d b c a a d c c a d Compute: a). R∪S’ b). R-S’ c). (Q, R ⋈ S) d). B,C(S) e). A=C(Q) f). R ⋈ S Note: take < to be alphabetic order on letters R.B<S.B∧R.C=S.C Exercise 2-4 Let R, S and S’be the relations shown as follows: YSLiu-DBS-Exercise
Exercise 3 • Show the basic functions of a DBMS. • Show the main DBMS components with their functions and the relations between the components • What is metadata and DD, and why is DD necessary? • What are the main contents of a DD ? • Briefly describe the major processes of a DB accessing YSLiu-DBS-Exercise
Exercise 4 • What are the performance measures of a disk, and their contents, respectively • What is a Block, its storage structure on a disk and access time? • Briefly describe the basic file organizing forms • Show the common and the different properties between B-tree and B+-tree YSLiu-DBS-Exercise
Exercise 4-1 • Let (2, 3, 5, 7, 11, 17, 19, 23, 29, 31) be a key set. Please create B-trees and B+-trees of the following orders on the key set, respectively: a) Order2 b) Order3 c) Order4 • On the B+-trees created in the above exercise5, show the B+-trees after the operations: • Inserting 9,10,8 • Deleting 23,19 YSLiu-DBS-Exercise
Exercise 4-2 • Suppose that a file contains 20,000 records and the primary key is 40 bytes long. Let a pointers, including that pointing to blocks and to records, be 10 bytes long, a block size be 1,024 bytes. If create a B+-tree initially ( each node is loaded as full as possible), please answer the questions: • How large is it suitable to select the order n of the B+-tree? • What is the length, i.e. the levels, of the B+-tree? • What are the numbers of the nodes on each level ? • How high is the B+-tree if the loading factor of a block is 70% YSLiu-DBS-Exercise
B0 R(d) R(e) R(c) B1 R(b) B2 B3 R(a) R(f) Exercise 4-3 • Suppose we have a bucket-hash file as shown in the figure. What will be happened and how can you do if the following operations occur: • Insert the records R(g)~R(j) into the bucketB0~B3, respectively. • Delete the records R(a) and R(b). • Insert the records R(k)~R(n) into the buckets B0~B3, respectively • Delete the records R(c) and R(d)。 YSLiu-DBS-Exercise
Exercise 5 • Write the queries in the exercise 2-2.7 with SQL. • Consider the DB including the following information: Student (S#: integer, Sname: string, Dept: string, Age: integer) Enrolled (S#: integer, Cname: string, Grade: integer) Class (Name: string, Meeting: time, Room, Dept: string, Fid) Faculty (Fid: integer, Fname: string, Dpet: string) Write the following requests in SQL: YSLiu-DBS-Exercise
Exercise 5-1 • Define the schema of the DB, including integrity constraints • Find the age of the oldest student who is either in CS dept or enrolled in a course taught by T. Dragon • Find the names of all classes that either meet in room E9-405 or have at least 50 students enrolled • Find the names of all students who are enrolled in two classes met at the same time • Print out the grade record of every student YSLiu-DBS-Exercise
Exercise 5-2 • Consider the following relation schemas: Seller (S#, Sname, Address, Phone) Articles (A#, Aname, Price) Sell (S#,A#,Amount) Write SQL queries that print out: • the total amount and sum of each article sold by 张三 • the average amount of each article sold by each seller at a time; • the name and the amount of the article whose amount sold by 李四 is largest YSLiu-DBS-Exercise
Exercise 5-3 • For the DB in the exercise 5.2, write SQL statements of the following updates: • Delete all the classes that is taught by R.Jhon for dept CS. • Delete all the information about faculty who have not yet taught any course • Change the mane of course taught by T. Richard for his own dept into DB Implement • Add a reenrollment record into Enrolled for every student who has not passed a course YSLiu-DBS-Exercise
Exercise 6 • Describe the basic steps and their main tasks of database design • Design a teaching management DB which contains the following information: • Course (C#, Cname, Point, Dept, Description); • Student (S#, Sname, Dept, Adress); • Teacher (T#, Tname, Position, Dept, Phone); • Offering (C#, Term, Time, Room, T#). Please: YSLiu-DBS-Exercise
Exercise 6 • Design an E-R diagram for the DB • Improved the E-R diagram designed in above a) to model the information about Enrollment and Grade Report. • Please give your other E-R designs for the above b) if any. What are the advantages and disadvanetages of each of the E-R designs, and which is your favor and why YSLiu-DBS-Exercise
Exercise 6-1 • Design a commercial DB that contains the following information: • Customer names, addresses and phones; • Merchandise names, types, prices, and quantities and dates that a customer purchases a item. Suppose that each customer has a different name and can buy the same items at different time. Please: 1). present the E-R model of the DB; 2). convert the E-R model into relation schemas and normalize them into 3NF relations YSLiu-DBS-Exercise
Exercise 6-2 • What is Relational Analysis and why needed ? • Present the definitions of 1NF~BCNF and the relationships among them • Suppose that we have the relation schema R(A,B,C,D,E) and its FDS: {ABC, CDE, BD, EA}. Show that The decomposition: {R1(A,B,C), R2(A,D,E)} is a lossless-join decomposition The decomposition: {R1(A,B,C), R2(C,D,E)} is not a lossless-join decomposition YSLiu-DBS-Exercise
Exercise 6-3 • Give the relation schema R(A,B,C,D) and the followings: FDS1: {CD, BC, CA} FDS2: {BC, DA} FDS3: {ABCD, DA} FDS4: {AB, BCD, AC} FDS5: {ABC, ABD, CA, DB} For each of the above FDS’, do identifying the candidate key(s) identifying the best NF that R satisfies if R is not in BCNF, decompose it into a set of BCNF relations. YSLiu-DBS-Exercise
Exercise 6-4 • Let relation schema R(A,B,C,D,E) have a FDS {A→BC,CD→E,B→D,E→A}, Please: • Proof {(A, B, C),(A, D, E)} is a loseless decomposition. • Present a loseless decomposition of R into BCNF relations • Prenset a loseless and dependency-proverving decomposition of R into 3NF relations. YSLiu-DBS-Exercise