关于人工智能:CSCV-460

CSCV 460 Assignment: Data Modeling and
Mapping

1. (30 points) Consider the following set 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 number,
   social security number, current address and phone, permanent
   address and phone, birth-date, sex, class (freshman, sophomore,
   …, graduate), major department, minor department (if
   any), and degree program (B.A., B.S., …, Ph.D.). Some user applications
   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, number
   of semester hours, level, and offering department. The value
   of course number is unique for each course.
   1
   (d) Each section has an instructor, semester, year, course, and section
   number. The section number distinguishes different 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 unspecified requirements,
   and make appropriate assumptions to make the specification
   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 current
   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 first elected, and the political Party to
   which he or she belongs (whose domain is {‘Republican’,‘Democrat’,
   2
   ‘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 congressperson
   voted on each bill (domain of Vote attribute is {‘Yes’,
   ‘No’,‘Abstain’,‘Absent’}. Draw an ER schema diagram for this application.
   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.
   NOTES: Please turn in your completed work to D2L Data Modeling and Mapping.