Database

1. Database Relational Model

2. The Relational Data Model Relational Schema Physical storage Data Modeling Complex file organization and index structures. E/R diagrams Tables: column names: attributes rows: tuples

3. Relational Data Model • Core of majority of modern databases • Virtually all business relies on some form of relational database • Solid theoretical/mathematical foundation

4. Relational Model Concepts • The relational Model of Data is based on the concept of a Relation. • A Relation is a mathematical concept based on the ideas of sets. • Order of tuples not important • Order of attributes not important (in theory)

5. RELATION • RELATION is A table of values • A relation may be thought of as a set of rows. • A relation may alternately be though of as a set of columns.

6. Relation Instance Name Address Telephone Ahmed 123 Main St 555-1234 Hassan 12 State St 555-1235 Ahmed 123 Main St 555-1235 Mona 456 Main St 555-2221 Sally 456 Main St 555-2221 Sally 456 Main St 555-2223 Hassan 12 State St 555-1235

7. ExampleState

8. Schema • The schema of a relation is the name of the relation followed by a parenthesized list of attributes (+ types of attributes). CoursesTaken(Student, Course, Grade) • A design in a relational model consists of a set of schemas. • Such a set of schemas is called a relational database schema.

9. Example Schema

10. Relation Schema relation name StockItem Attribute Domain ItemID string(4) Description string(50) Price currency/dollars Taxable boolean set of attributes attribute names attribute domains

11. Relational schema • Is a direct map from ER diagram into basic table • For example, the schema (ID, phone, name, birth_date, address)

12. Banking Example branch (branch_name, branch_city, assets) customer (customer_name, customer_street, customer_city) account (account_number, branch_name, balance) loan (loan_number, branch_name, amount) depositor (customer_name, account_number) borrower(customer_name, loan_number)

13. Example Database Schema • Student (Id: INT, Name: STRING, Address: STRING, Status: STRING) • Professor(Id: INT, Name: STRING, DeptId: DEPTS) • Course(DeptId: DEPTS, CrsName: STRING, CrsCode: COURSES) • Transcript(CrsCode: COURSES, StudId: INT, Grade: GRADES, Semester: SEMESTERS) • Department(DeptId: DEPTS, Name: STRING)

14. Key Constraints • key of R: A set of attributes SK of R such that no two tuples will have the same value for SK • If a relation has severalcandidate keys, one is chosen arbitrarily to be the primary key. The primary key attributes are underlined. • Indicate a key by underlining the key attributes. • Example: If name is a key for Beers: Beers(name, manf)

15. Entity Set to Relation name category price Product Product(name, category, price) name category price gizmo gadgets $19.99

16. Relationships to Relations price name category Start Year name makes Company Product Stock price Makes(product-name, product-category, company-name, year) Product-name Product-Category Company-name Starting-year gizmo gadgets gizmoWorks 1963

17. Relationships to Relations price name category Start Year name makes Company Product Stock price No need for Makes. Modify Product: name category price StartYear companyName gizmo gadgets 19.99 1963 gizmoWorks

18. carries Aircraft Flight Flight (flight#, arrival_airport, depart_airport, arrival_time, depart_time) Airport (code, city) Aircraft (aircraft, no_of_seats) Identifier of flight seems strange. ‘Flight_no’ alone should identify a flight. uses Airport ERD FROM DATASTORES FLIGHTS

19. Aircraft Flight (flight#, arrival_airport, depart_airport, arrival_time, depart_time) Stopover (flight_no, code, arrival_time, depart_time) Airport (code, city) Aircraft (aircraft, no_of_seats) carries Stopover Flight Departs_from Leaves_from Stops_at Arrives_at Airport ERD FROM DATASTORES FLIGHTS

20. Multi-way Relationships to Relations address name Product Purchase Store price name Person Purchase( , , ) ssn name

21. name addr name manf Likes Drinkers Beers • For one-one relation Married, we can choose either husband or wife as key. 1 2 Buddies Favorite husband wife Married Likes(drinker, beer) Favorite(drinker, beer) Married(husband, wife) Buddies(name1, name2)

22. Weak Entity Sets, Relationships  Relations • Relation for a weak E.S. must include its full key (i.e., attributes of related entity sets) as well as its own attributes. • A supporting (double-diamond) relationship yields a relation that is actually redundant and should be deleted from the database schema.

23. Representing Entity Sets With Simple Attributes • A strong entity set reduces to a schema with the same attributesstudent(ID, name, tot_cred) • A weak entity set becomes a table that includes a column for the primary key of the identifying strong entity set section ( course_id, sec_id, sem, year )

24. Example name name @ @ Logins Hosts Hosts(hostName) Logins(loginName, hostName) At(loginName, hostName, hostName2) • In At, hostName and hostName2 must be the same host, so delete one of them. • Then, Logins and At become the same relation; delete one of them. • In this case, Hosts’ schema is a subset of Logins’ schema. Delete Hosts?

25. Converting Non-identifying Attributes • Single-valued (standard attribute) • Create a table column for each • Derived • Omit: these values are not stored in our tables • Later, we can produce these values using a query • Multi-valued • Relational model does not directly support! • However, as we have discussed, a multi-valued attribute can be conceptualized as a new (weak) entity, thus implying a separate table.

26. Composite and Multivalued Attributes Composite attributes are flattened out by creating a separate attribute for each component attribute Example: given entity set instructor with composite attribute name with component attributes first_name and last_name the schema corresponding to the entity set has two attributes name_first_name and name_last_name Prefix omitted if there is no ambiguity Ignoring multivalued attributes, extended instructor schema is instructor(ID, first_name, middle_initial, last_name, street_number, street_name, apt_number, city, state, zip_code, date_of_birth)

27. Composite and Multivalued Attributes A multivalued attribute M of an entity E is represented by a separate schema EM Schema EM has attributes corresponding to the primary key of E and an attribute corresponding to multivalued attribute M Example: Multivalued attribute phone_number of instructor is represented by a schema:inst_phone= (ID, phone_number) Each value of the multivalued attribute maps to a separate tuple of the relation on schema EM For example, an instructor entity with primary key 22222 and phone numbers 456-7890 and 123-4567 maps to two tuples: (22222, 456-7890) and (22222, 123-4567)

28. Converting Binary Relationships • One-to-onerelationships • Consider the 2 associated entity tables. • The foreign key column(s) can be with either entity • As before, copy the primary key column(s) of the related table • Note: in a 1:1 relationship, the two entities often use the same identifier, in which case the existing primary key columns serve the “dual role” of both primary and foreign keys • A separate foreign key column is then unnecessary!

29. Converting Binary Relationships • One-to-manyrelationships • Consider the 2 associated entity tables. • Within the “many side” entity’s table, we need to have foreign key column(s) referring to the related “one side” entity instances • We use the identifier of the related entity to define the foreign key column(s) • In other words, we include a column (a “copy”) of primary key values from the related table • The copied primary key values we call “foreign keys.”

30. Schema Diagram

31. REVIEW Reverse engineer this relational schema to find an equivalent ER schema.

32. Converting Binary Relationships • Many-to-manyrelationships • Relational model does not directly support! • However, each many-to-many relationship can be conceptualized as a new (associative) entity, thus implying a separate table. • The identifier for the associative entity is the combination of the identifiers for the two related entities. • Thus, for the separate table we create for an M:M relationship, its primary key columns include the primary key columns for both of the related tables.

33. Finding the Keys If the relation comes from a many-many relationship, the key of the relation is the set of all attribute keys in the relations corresponding to the entity sets name buys Person Product price name ssn date buys(name, ssn, date)

34. PREVIEW: ER to Relational

35. EER Bank Schema

36. Step 1: Regular Entities • Regular entity types become relations • include all simple attributes • include only components of compound attributes • keys become primary keys • if multiple keys (candidates) select a primary key CUSTOMER(Ssn, Name, Addr, Phone)

37. Step 1: Regular Entities BANK(Code, Name, Addr) ACCOUNT(Acct_no, Type, Balance) LOAN(Loan_no, Type, Amount)

38. Step 2: Weak Entities • Weak entity types become relations • include all simple attributes • include only components of compound attributes • create a primary key from partial key and key of owning entity type (through identifying relationship) • attributes acquired through identifying relationship become a foreign key* * typically, deletions and insertions will be propagatedthrough this foreign key

39. Step 2: Weak Entities • Weak entity types become relations BANK_BRANCH(Bank_code, Branch_No, Addr) FK BANK(Code, Name, Addr)

40. Step 3: Binary 1:1 Relationships • Approach 1: Foreign Key EMPLOYEE(Ssn, Name, …) FK DEPARTMENT(Name, Number, Mgr, Mgr_start_date)

41. Step 3: Binary 1:1 Relationships • Approach 2: Merged Relation AJB(x, y, p, q, r) or AJB(x, y, p, q, r)

42. Step 4: Binary 1:N Relationships • 1:N Relationships become foreign key at N side • any relationship attributes also go to N side LOAN(Loan_no, Type, Amount, Bank, Branch) BANK_BRANCH(Bank_code, Branch_No, Addr)

43. Step 4: Binary 1:N Relationships • 1:N Relationships become foreign key at N side • any relationship attributes also go to N side ACCOUNT(Acct_no, Type, Balance, Bank, Branch) BANK_BRANCH(Bank_code, Branch_No, Addr)

44. Step 5: Binary M:N Relationships • M:N Relationships must become a new relation • contains FKs to both related entities • combined FKs become PK for new relations • relationship attributes go in new relation CUSTOMER(Ssn, Name, Addr, Phone) A_C(Acct, Cust) ACCOUNT(Acct_no, Type, Balance, Bank, Branch)

45. Step 6: Multivalued Attributes • Multivalued attributes must become new relations • FK to associated entity type • PK is whole relation DEPARTMENT(Name, Number, Mgr, Mgr_start_date) DEPT_LOCATIONS(DName, Dno, Location)

46. Step 7: N-ary Relationships • Non-Binary Relationships become new relations • FKs to all participating entity types • Combine FKs to make a PK (exclude entities with max participation of 1) • Include any relationship attributes SUPPLIER(SName) PROJECT(Proj_name) PART(Part_no) SUPPLY(SName, PName, Part, Quantity)

47. Completed Bank Schema CUSTOMER(Ssn, Name, Addr, Phone) BANK(Code, Name, Addr) ACCOUNT(Acct_no, Type, Balance, Bank, Branch) LOAN(Loan_no, Type, Amount, Bank, Branch) BANK_BRANCH(Bank_code, Branch_No, Addr) A_C(Acct, Cust) L_C(Loan, Cust) BANK_BRANCH(Bank_code) refers to BANKLOAN(Bank,Branch) refers to BANK_BRANCH ACCOUNT(Bank,Branch) refers to BANK_BRANCH A_C(Acct) refers to ACCOUNT A_C(Cust) refers to CUSTOMER L_C(Loan) refers to LOAN L_C(Cust) refers to CUSTOMER

48. Bank Schema: MS Access

49. Exercise A university database contains information about professors (identified by social security number) and courses (identified by courseid). Professors teach courses; each of the following situations concerns the Teaches relationship set. For each situation, draw an ER diagram that describes it. • Professors can teach the same course in several semesters, and each offering must be recorded.

50. Exercise Professors can teach the same course in several semesters, and only the most recent such offering needs to be recorded.