Question

In: Computer Science

Consider the following set of requirements for a UNIVERSITY database that is used to keep track...

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, birthdate, 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. Each department has a faculty as a Chair to manage it.

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

(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).

(f) The database keeps track of each faculty’s name, faculty ID, SSN, address, email and office location.

(g) Each course section will have a faculty assigned as instructor.

(h) Each College controls several departments. Each College is described by a name, college ID and college office location. A faculty assigned as a Dean to manage a college.

Draw an ER diagram based on the requirement described here.

You should draw entity types, attributes and relationships in the ER diagram [10 points].

Specify key attributes of each entity type [2 points].

Use hierarchy structure to represent composite attributes. Use double lined oval to represent multi-value attributes [2 points].

Solutions

Expert Solution

Below is the ER diagram of the given scenario. It consist of 5 entities connected via 4 relationship. Each entity has certain set of attribute and primary key is mention as underlined attribute.

Attribute permanent address of entity Student is shown as composite attribute consisting of city, state, zip. It is shown in hierarchy structure.

Attribute College of entity department is shown as multivalue attribute as a department can have multiple colleges under it.

Cardinality in the ERD is shown in crow's foot notation.


Related Solutions

Consider the following set of requirements for a university information system that is used to keep...
Consider the following set of requirements for a university information system that is used to keep track of students’ transcripts. R1: The university keeps track of each student's name, student number, class, and degree program. R2. The university consists of various departments. Each department is described by a name, department code and phone. R3. Each course has a course number, course name, and credits. R4. Each section has an instructor, course number, and section number. There might be multiple sections...
14.19. Suppose that we have the following requirements for a university database that is used to...
14.19. Suppose that we have the following requirements for a university database that is used to keep track of students’ transcripts: a. The university keeps track of each student’s name (Sname), student number (Snum), Social Security number (Ssn), current address (Sc_addr) and phone (Sc_phone), permanent address (Sp_addr) and phone (Sp_phone), birth date (Bdate), sex (Sex), class (Class) (‘freshman’, ‘sophomore’, … , ‘graduate’), major department (Major_code), minor department (Minor_code) (if any), and degree program (Prog) (‘b.a.’, ‘b.s.’, … , ‘ph.d.’). Both...
Design a database through the EER diagram to keep track of the teams and games of...
Design a database through the EER diagram to keep track of the teams and games of a sport league. Assume that the following requirements are collected (the English description of cardinal ration and partial/complete participate is NOT required, but you still need to provide the total/partial and cardino ration in your EER diagram) : The database has a collection of TEAM. Each Team has a unique name, players, and owner. The database also keeps the records of PLAYERS. Each player...
Ava wants to use a database to keep track of the data recordsfor her insurance...
Ava wants to use a database to keep track of the data records for her insurance company and to enforce the following business policies/requirements: USE MS ACCESS TO CREATE A DATABASE & RELATIONASHIP-Every customer must be uniquely identified.-A customer can have many insurance policies.-Every insurance policy must be uniquely identified.-An insurance policy must belong to a valid customer.-Every customer must be served by a valid insurance agent (employee).-An insurance agent (employees) serves many customers.-Every insurance agent (employee) must be uniquely...
1. A cosmetic product retailer needs to create a database to keep track of the information...
1. A cosmetic product retailer needs to create a database to keep track of the information for its business operations. The company has a web site that posts all its products. The product information includes product ID, product name, description, and unit price. The company also needs to keep track of customers’ information, including customer names, their shipping addresses, and the email address. The company creates an account for each customer for identification and tracking purpose. A customer can purchase...
Database Design and SQL The following relations keep track of airline flight information: Flights (flno: integer,...
Database Design and SQL The following relations keep track of airline flight information: Flights (flno: integer, from : string, to: string, distance: integer, departs: time, arrive: time, price: integer) Aircraft (aid: integer, aname : string, cruisingrange: integer) Certified (eid: integer, aid: integer) Employees (eid: integer, ename : string, salary: integer) The Employees relation describe pilots and other kinds of employees as well. Every pilot is certified for some aircraft and only pilots are certified to fly. Based on the schemas,...
A retailer, Continental Palms Retail (CPR), plans to create a database system to keep track of...
A retailer, Continental Palms Retail (CPR), plans to create a database system to keep track of the information about its inventory. CPR has several warehouses across the country. Each warehouse is uniquely named. CPR also wants to record the location, city, state, zip, and space (in cubic meters) of each warehouse. There are several warehouses in any single city. CPR stores its products in the warehouses. A product may be stored in multiple warehouses. A warehouse may store multiple products....
Scenario: An auto shop is designing a database to keep track of repairs. So far, we...
Scenario: An auto shop is designing a database to keep track of repairs. So far, we have this UNF relation, with some sample data shown. Normalize to 1NF. REPAIRS: # VIN, Make, Model, Year, ( Mileage, Date, Problem, Technician, Cost ) VIN Make Model Year Mileage Date Problem Technician Cost 15386355 Ford Taurus 2000 128242 6/6/2014 Won’t start Gary $300 15386355 Ford Taurus 2000 129680 6/20/2014 Tail light out Trisha 43532934 Honda Civic 2010 38002 6/18/2014 Brakes slow Gary $240...
Scenario: A builder needs a database to keep track of contractors he hires for various projects....
Scenario: A builder needs a database to keep track of contractors he hires for various projects. So far, we have this 2NF relation, with sample data shown. Normalize to 3NF. CONTRACTOR: # ConID, Lname, Fname, JobTitle, Company, Street, City, State, Zip, CompanyPhone, CellPhone ConID Lname Fname JobTitle Company Street City State Zip Phone CellPhone 2 Garcia Mary Carpenter Construct Co 123 Main Portland OR 97204 823-1234 645-5423 14 Jones Tomas Welder Construct Co 123 Main Portland OR 97204 823-1234 344-3475...
Consider a university database for the scheduling of classrooms for final exams. This database could be...
Consider a university database for the scheduling of classrooms for final exams. This database could be modeled as the single entity set exam, with attributes course-name, section-number, room-number, and time. Alternatively, one or more additional entity sets could be defined, along with relationship sets to replace some of the attributes of the entity set, as • course with attributes name, department, and c-number • section with attributes s-number and enrollment, and dependent as a weak entity set on course •...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT