1. (30 points) Consider the following of requirements for a university

database that is used to keep track of students' transcripts.

(a) The university keeps track of each student's name, student num-

ber, social security number, current address and phone, perma-

nent address and phone, birth-date, sex, class (freshman, sopho-

more, ..., graduate), major department, minor department (if

any), and degree program (B.A., B.S., ..., Ph.D.). Some user ap-

plications need to refer to the city, state, and zip of the student's

permanent address, and to the student's last name. Both social

security number and student number have unique values for each

student.

(b) Each department is described by a name, department code, office

number, office phone, and college. Both name and code have

unique values for each department.

(c) Each course has a course name, description, course number, num-

ber of semester hours, level, and offering department. The value

of course number is unique for each course.

(d) Each section has an instructor, semester, year, course, and section

number. The section number distinguishes dierent sections of

the same course that are taught during the same semester/year;

its values are 1, 2, 3, ...; up to the number of sections taught

during each semester.

(e) A grade report has a student, section, letter grade, and numeric

grade (0, 1, 2, 3, 4 for F, D, C, B, A, respectively).

Design an ER schema for this application, and draw an ER diagram for

that schema. Specify key attributes of each entity type and structural

constraints on each relationship type. Note any unspeci ed require-

ments, and make appropriate assumptions to make the speci cation

complete.

2. (20 points) Design and ER schema for keeping track of information

about votes taken in the U.S. House of Representatives during the cur-

rent two-year congressional session. The database needs to keep track

of each US state's Name (e.g., `Arizona', `Texas', `California') and

include the Region of the statement (whose domain is`Northeast', `Southeast', `Southwest', `West'

). States have between one (AK, DE, MT, ND, SD, VT, and WY) and 52 (CA) representatives. There

are 435 congresspersons in the U.S. House of Representatives. Each

CONGRESS_PERSON in the House of Representatives is described

by his or her Name, plus the District represented, the Start_date

when the congressperson was rst elected, and the political Party to

which he or she belongs (whose domain is {`Republican', `Democrat', `Independent', `Other'}

). The database keeps track of each

BILL (i.e., proposed law), including the Bill_name, the Date_of_vote

on the bill, whether the bill Passed_or_failed whose domain is {`Yes', `No'} , and the

Sponsor (the congressperson(s) who sponsored|that

is, proposed|the bill). The database keeps track of how each con-

gressperson voted on each bill (domain of Vote attribute is {`Yes',

`No', `Abstain', `Absent'}. Draw an ER schema diagram for this ap-

plication. State clearly any assumptions you make.

3. (20 points)

Try to map the schema in Figure 1 into an ER schema. This is part of

a process known as reverse engineering, where a conceptual schema is

created for an existing implemented database. State any assumptions

you make.

4. (30 points)

Figure 2 shows an ER schema for a database that may be used to keep

track of transport ships and their locations for maritime authorities.

Map this schema into a relational schema, and specify all primary keys

and foreign keys

Date Time stamp Time SHIP MOVEMENT Longitude Latitude HISTORY Tonnage Hull NTYPE SHIP SHIP TYPE Owner tart, date