1 / 23

Principles of Database Systems

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.

cedric
Download Presentation

Principles of Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Principles of Database Systems Exercises Yunsheng Liu

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. Exercise 2-3 • What kinds of relational operations form a relational algebra? Why is it? • Let R, S be the relations shown as follows: Ｒ： Ａ　 Ｂ Ｓ： Ｂ Ｃ a b b c d e c a b c e dCompute: (1)Ｒ∪Ｓ： (2)R∩S； (3)Ｒ－Ｓ；　 (4)Ｒ⋈ S； （5）A(R)； (6) A=C(R×S)； (6) R⋈B＜CS, where ＜represent alphabetic YSLiu-DBS-Exercise

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

  21. 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: {ABC, CDE, BD, EA}. 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

  22. Exercise 6-3 • Give the relation schema R(A,B,C,D) and the followings: FDS1: {CD, BC, CA} FDS2: {BC, DA} FDS3: {ABCD, DA} FDS4: {AB, BCD, AC} FDS5: {ABC, ABD, CA, DB} 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

  23. 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

More Related