CSCV 460留学生讲解、辅导Data Modeling、讲解Python编程、辅导Java/c++
辅导Python编程|辅导R语言编程
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.
3Figure 1: The schema to be mapped into an ER schema
4Figure 2: The ER schema for a SHIP TRACKING database