1 / 23

1.(5) Describe the working process with a database system. a) database definition

1.(5) Describe the working process with a database system. a) database definition By the database definition, we create an entity-relationship diagram for a realistic world problem, which is then transformed into a database schema. b) populating tables with data

koren
Download Presentation

1.(5) Describe the working process with a database system. a) database definition

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. 1.(5) Describe the working process with a database system. • a) database definition • By the database definition, we create an entity-relationship diagram for a realistic world problem, which is then transformed into a database schema. • b) populating tables with data • The second phase is the database construction, by which a database is initially populated with data. • c) database manipulation • Once a database is constructed, one can conduct some operation on it, such as data insertion, data deletion and data updating, which changes the database from a state to another state. It is the responsibility of a database system to guarantee that the database goes from one correct state to another. ACS-3902 Yangjun Chen

  2. 2.(10) Describe the three-schema architecture of a database system and the main functionality of each level in this architecture. • a) internal level • The first level is the internal level, which possesses an internal schema. It describes the physical storage structure of the database. The internal schema uses a physical data model and describes the complete details of data storage and access paths for the database. • b) conceptual level • The second level is the conceptual level, which has a conceptual schema. It describes the structure of the whole database for a community of users. The conceptual level hides the details of physical structures and concentrates on entity types, data types, relationships, user operations, and constraints. A high level data model or an implementation data model can be used at this level. ACS-3902 Yangjun Chen

  3. c) external level The third level is the so-called external level or the view level, which includes a number of external schemas or user views. It describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group. A high level data model or the implementation data model can be used at this level. ACS-3902 Yangjun Chen

  4. 3.(10) Explain the following concepts: • (a) superkey • (b) key • (c) candidate key • (d) primary key • (e) foreign key • key constraints • a superkey is any combination of attributes that uniquely • identify a tuple: t1[superkey]  t2[superkey]. • a key is superkey that has a minimal set of attributes ACS-3902 Yangjun Chen

  5. If a relation schema has more than one key, each of them is called a candidate key. • one candidate key is chosen as the primary key (PK) • foreign key (FK) is defined as follows: • i) Consider two relation schemas R1 and R2; • ii) The attributes in FK in R1 have the same domain(s) as the primary key attributes PK in R2; the attributes FK are said to reference or refer to the relation R2; • iii) A value of FK in a tuple t1 of the current state r(R1) either occurs as a value of PK for some tuple t2 in the current state r(R2) or is null. In the former case, we have t1[FK] = t2[PK], and we say that the tuple t1 references or refers to the tuple t2. ACS-3902 Yangjun Chen

  6. 4.(15) Draw an ER-diagram to describe the following real world problem. • (a) A university is organized into faculties. • (b) Each faculty has a unique name, ID and number of professors and a specific professor is chosen as the faculty head. • (c) Each faculty provides a number of courses. • (d) Each course has a unique name and courseID. • (e) Each professor has a name, SIN, address, salary, sex and courses taught by him/her. • (f) Each professor belongs to a faculty and can teach several sections of a course. • (g) Each student has a name, ID, SIN, address, GPA, sex, and major. • (h) Each student can choose one faculty as his/her major faculty and take several courses with certain credit hours. Some of the courses are mandatory and some are optional. ACS-3902 Yangjun Chen

  7. ER-model: name salary ID addr. SIN N belong professor 1 NoProf F-Id sex F-name M startdate 1 teach head faculty 1 N M 1 sections sectionId N provide M choose has 1 course N ID name addr. N SIN take M couresId student major mandatory-optional creditHours name sex birthdate ACS-3902 Yangjun Chen

  8. 5.(30) Linear Hashing - collision resolution strategy: chaining - split rule: load factor > 0.7 - initially M = 4 (M: size of the primary area) - hash functions: hi(key) = key mod 2i  M (i = 0, 1, 2, …) - bucket capacity = 2 Trace the insertion process of the following keys into a linear hashing file: 24, 7, 10, 5, 14, 4, 1, 8, 2, 3, 17, 13, 15. ACS-3902 Yangjun Chen

  9. 24 5 10 14 7 4 • The first phase – phase0 • when inserting the sixth record we would have • but the load factor 6/8= 0.75 > 0.70 and so bucket 0 must be split (using h1 = Key mod 2M): 24 4 5 10 14 7 n=0 before the split (n is the point to the bucket to be split.) 0 1 2 3 n=1 after the split load factor: 6/10=0.6 no split 0 1 2 3 4 ACS-3902 Yangjun Chen

  10. 24 5 1 10 14 7 4 24 5 10 14 7 4 insert(1) 0 1 2 3 4 n=1 load factor: 7/10=0.7 no split 0 1 2 3 4 ACS-3902 Yangjun Chen

  11. insert(8) 24 8 5 1 10 14 7 4 24 5 1 10 14 7 4 0 1 2 3 4 n=1 load factor: 8/10=0.8 split using h1. ACS-3902 Yangjun Chen

  12. insert(2) 24 8 24 8 1 1 10 14 10 14 7 7 4 4 5 0 1 2 3 4 5 n=2 load factor: 8/12=0.66 no split 5 0 1 2 3 4 5 ACS-3902 Yangjun Chen

  13. 24 8 1 10 2 7 4 5 14 0 1 2 3 4 5 24 8 1 10 14 7 4 5 n=2 load factor: 9/12=0.75 split using h1. overflow 2 ACS-3902 Yangjun Chen

  14. insert(3) 24 8 1 10 2 7 3 4 5 14 24 8 1 10 2 7 4 5 14 n=3 load factor: 10/14=0.714 split using h1. ACS-3902 Yangjun Chen

  15. insert(17) 24 8 1 10 2 3 4 5 14 7 n=4 The second phase – phase1 n = 0; using h1 = Key mod 2M to insert and h2 = Key mod 4M to split. 8 24 1 10 2 3 4 5 14 7 ACS-3902 Yangjun Chen

  16. insert(13) 8 24 1 17 10 2 3 4 5 14 7 n=0 load factor: 11/16=0.687 no split. 8 24 1 17 10 2 3 4 5 14 7 ACS-3902 Yangjun Chen

  17. 8 24 1 17 10 2 3 4 5 13 14 7 n=0 load factor: 12/16=0.75 split bucket 0, using h2. 1 17 10 2 3 4 5 13 14 7 8 24 ACS-3902 Yangjun Chen

  18. insert(15) 1 17 10 2 3 4 5 13 14 7 8 24 1 17 10 2 3 4 5 13 14 7 15 8 24 n=1 load factor: 13/18=0.722 split bucket 1, using h2. 1 17 10 2 3 4 5 13 14 7 15 8 24 ACS-3902 Yangjun Chen

  19. b f a b • 6.(15) Given the following B+-tree, trace the deletion sequence: h, c, • e, f. Here, we assume that each internal node can contain at most two • keys and each leaf node can contain at most two value/point pairs. c c e f h Fig. 1 ACS-3902 Yangjun Chen

  20. c a e b e c e f c a b a b e f remove h: remove c: ACS-3902 Yangjun Chen

  21. e a f b b remove e: c a b f remove f: e a a a a ACS-3902 Yangjun Chen

  22. 7.(15) Given the relation schemas shown in Fig. 2, construct expressions • (using relational algebraic operations) to evaluate the following query: • Find the names of employees who works on all the projects • controlled by department ‘Business Computing’. EMPLOYEE fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno DEPARTMENT Dname, dnumber, mgrssn, mgrstartdate Fig. 2 PROJECT WORKS_ON Pname, pnumber, plocation, dnum Essn pno, hours ACS-3902 Yangjun Chen

  23. : SSNS EMP_PNOS DEPT_P DN dnumber(Dname = ‘Business Computing’(Department)) DEPT_P PNUMBER(DNDN.dnumber = PROJECT.dnumber(PROJECT)) EMP_PNOS  ESSN,PNO(WORK_ON) RESULT  FNAME, LNAME(SSNS * EMPLOYEE) ACS-3902 Yangjun Chen

More Related