Chris Pollett >
Old Classes
> |
HW2 Solutions Page3.16 This problem was to design an ER Schema for the university database described. Then draw the ER diagram. STUDENT Name(FName, LName), Number, SSN, StudentNum, CurrentAddr(City, State, ZIP, Phone), PermanentAddr(City, State, ZIP, Phone), BirthDate, Sex, Major, Minor, Degree Name was made composite so user applications could access last name. CurrentAddr and PermanentAddr were made composite so user applications could acces City, State and ZIP. The only attribute that is not a string or number is Degree. A_{Degree}(e) for any student entity e must be a singleton set of the form {B.A.}, ..., {Ph.D}. DEPARTMENT Name, DeptCode, OfficeNum, OfficePhone, College COURSE CourseName, Description, CourseNum, NumOfSemHours, Level, OfferingDept SECTION Instructor, Semester, Year, Course, SectionNum The only attribute that is further restricted is SectionNum. A_{SectionNum}(e) for any student entity e must be a singleton set of the form {1}, {2}, {3},... GRADE_REPORT STUDENT X SECTION X Letter X NumGrade Letter is a relationship attribute which corresponds to the set {A, B, C, D, E, F} NumGrade is a relationship attribute which corresponds to the set {0, 1, 2, 3, 4} This is a many to many relationship. Other Assumptions: The other minimal assumptions I made about the entities described can be summarized in the following relationships: OFFERS DEPARTMENT X COURSE The ratio DEPARTMENT:COURSE is 1:N. A COURSE must be offered by some DEPARTMENT so participates totally in this relationship HAS COURSE X SECTION The ratio COURSE:SECTION is 1:N. Both entities totally participate in this relationship. i.e., a course must have at least one section and a section must be a section of some course. ENROLLS STUDENT X SECTION The ratio STUDENT:SECTION is N:M SECTION participates totally in this relationship 3.23 (a) List the entity types in the ER diagram 3.17 from book. BANK, ACCOUNT, LOAN, CUSTOMER (b) Describe the weak entity type in this diagram. The weak entity is BANK-BRANCH. Its partial key is BranchNo and its identifying relationship is Bank. (c) What constraints do the partial key and the identifying relationship of the weak entity type specify in this diagram? The partial key BranchNo must uniquely specify the BANK-BRANCH given the BANK Code. That both sides of the identifying relationship have total participation means that each BANK must have at least one BANK-BRANCH, although it can have many; and means that every BANK-BRANCH must be the BANK-BRANCH of some BANK (and so can be identified by the (BranchNo, Code) pair). (d) List the names of all relationship type, and specify (min, max) constraints for the entity types participating in them. BRANCHES card(BANK, BRANCHES) = (1,N) card(BANK-BRANCH, BRANCHES) = (1,1) branch can be branch of at most one bank ACCTS card(ACCOUNT, ACCTS) = (1,1) max b/c accounts can only be at one bank card(BANK-BRANCH, ACCTS) = (0,N) Similar reasoning goes into constraints below. A-C card(ACCOUNT, A-C) = (1,N) card(CUSTOMER, A-C) = (0,M) LOANS card(BANK-BRANCH, LOANS) = (0,N) card(LOAN, LOANS) = (1,1) L-C card(LOAN, L-C) = (1,M) card(CUSTOMER, L-C) = (0,N) (e) List concisely the user requirements that led to this diagram. 1. A BANK has a Code, Name and Addr. The Code uniquely specifies the BANK. Every BANK must have a BRANCH_BANK. 2. An ACCOUNT has a AccntNo, Balance and Type. The AccntNo uniquely specifies the ACCOUNT. 3. A CUSTOMER has a phone, SSN, Name and Addr. The SSN uniquely specifies the CUSTOMER. 4. A LOAN has a LoanNo, Amount, and Type. The LoanNo uniquely specifies the LOAN. 5. BANK_BRANCH has a BranchNo and Addr and is uniquely specified by the BranchNo and the Code of a BANK. 6. An ACCOUNT belong to at least one (maybe more) CUSTOMER and a CUSTOMER can have many ACCOUNTS. 7. An ACCOUNT is held at exactly one BANK-BRANCH and a BANK-BRANCH may have zero or more ACCOUNTS. 8. An LOAN is given to at least one (maybe more) CUSTOMER and a CUSTOMER can have many LOANs. 9. An LOAN is from at exactly one BANK-BRANCH and a BANK-BRANCH may have zero or more LOANs. (f) Suppose that every customer must have at least one account and at most two loans, and that branch cannot handle more than 1000 loans. How would this show up in (min, max) contraints? card(BANK-BRANCH, LOANS) = (0,1000) card(CUSTOMER, A-C) = (1,M) card(CUSTOMER, L-C) = (0,2) 4.20 I assumed there was an entity type MUSEUM. I assumed MUSEUMs put on EXHIBITIONs and own COLLECTIONs. Since COLLECTIONs can be personal they don't have to belong to a MUSEUM. STATUEs have a Subject (a decription of who it is) and a material (marble, bronze, etc.) SAMPLE SESSION WITH SIGMA Script started on Tue Mar 12 18:29:12 2002 |eniac:157a.4.02s:101>telnet sigma Trying 130.65.85.63... Connected to sigma. Escape character is '^]'. SunOS 5.8 login: pollett Password: Oracle Database User: 1.) CS157a students, please 'source /oraclesw/csh_env/cs157a/enviro/cs157aenv' to set environment variables 2.) CS157b students, please 'source /oraclesw/csh_env/cs157b/enviro/cs157benv' to set environment variables 3.) CS174 students, please 'source /oraclesw/csh_env/cs174/enviro/cs174env' to set environment variables 4.) CS257 students, please 'source /oraclesw/csh_env/cs257/enviro/cs257env' to set environment variables 5.) To use PICO as sqlplus text editor, enter 'DEFINE _EDITOR=pico' (without quote) after login Oracle (ie after sqlplus) 6.) To use JDBC or SQLJ, please run 'source /usr/local/bin/jdbcenv' (without quote) to set enviroment variables Done! You can start sqlplus now! JDBC & SQLJ path set Example programs are in /oraclesw/oracle817/jdbc/demo/samples and /oraclesw/oracle817/sqlj/demo |sigma:pollett:2>sqlplus SQL*Plus: Release 8.1.7.0.0 - Production on Tue Mar 12 18:29:42 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. Enter user-name: pollett Enter password: Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production SQL> select * from user_tables; no rows selected SQL> quit Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production |sigma:pollett:3>exit logout Connection closed by foreign host. |eniac:157a.4.02s:102>exit script done on Tue Mar 12 18:31:28 2002 |