School of Computing and Information Sciences



January 16, 2020Assignment-1Due: January 23, 2020 COP-4710For the following problems, design ER schema and show the ER Diagrams using the symbolism used in the class.PROBLEM-1: [8]Consider the following set of requirements for a UNIVERSITY database that is used to keep track of students’ transcripts.The university keeps track of each student’s name, a unique student number, a unique Social Security number, address (split into street address, city, state, and zip code), phone number, birth date, sex, level (Freshman, etc.), major department, and degree program (BS, MS, PhD). Each department is described by a unique name, a unique department code, office phone number, and the college it belongs to.Each course in the university catalog has course name, a unique course number, description, number of credit hours, and offering department. Each section offered has an instructor, semester, year, course, and section number. The section number distinguishes sections of the same course that are taught during the same semester/year, its values being 1, 2, 3, …, up to the number of sections taught during each semester.For each section of a course that a student enrolls in, (s)he receives a grade. Each grade is described by a letter grade (F, D, C, B, A), and a numeric grade (0,…, 4).The database keeps track of the sections that a student has enrolled in, and the grade received in it.Design an ER Schema for this application, and draw its ER Diagram using the notations used in class. Specify key attributes of each Entity Type, and structural constraints (using max-min notation) on each Relation Type. For incomplete specifications, if any, make appropriate assumptions, and document them precisely.PROBLEM-2:[12]Consider the following set of requirements for an airline database that is used to keep track of flights, passengers, employees etc. for an airline. You will be graded not only on the correctness and completeness of the design but also on its quality.It is important to keep track of each passenger's name, address, phone number and meal preference (values could be vegetarian, sea-food, italian, anything). At times, it becomes necessary to refer to the city, state, and zip of the passenger's address.Each flight is described by its number, dep_city, arr_city, dep_time and arr_time. For this assignment, we will assume that all entries in the database are direct flights only. The flights are uniquely identified by their number.Obviously, it is important to keep track of all airline personnel. For each person, we need to keep track of the unique employee number, name, address and salary. The airline keeps track of all departures for one year. For each departure, we need to have the date of departure, the flight that it represents, all personnel who are going to be assigned to it, and all passengers that are booked on it.For each type of plane in the whole world, the airline wants to keep track of its model number and manufacturer. For example, the manufacturer of a plane whose model number is 747 is Boeing Corporation.Each aircraft owned and operated by the airline is assigned a unique serial number, and the airline keeps track of what type of plane it is. For example, serial # of a specific plane may be A1234 and type may be 747. Each departure is assigned a specific aircraft owned by the company.There is a select group of personnel called pilots for whom the information about which types of planes can they fly along with the date when they received their pilot licenses is kept. 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. Specify the structural constraints using partial/total participation and specification of cardinality ratio. If you find that the specifications are incomplete, then make appropriate assumptions and document them precisely. Do a complete job.Your design must follow the user requirements. If the user wants an entity type, it must show up in your design.Submit:The hard copy of your assignment at the beginning of the class on the due date. In addition to this, submit the soft copy “xyzassignment1.pdf” (xyz are your initials) using the canvas system. Good Luck! ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download