HW2 Solutions Page

Return to homework page.

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