Reference no: EM132394618 , Length: 6 Pages

Assignment -

Directions:

i. Identify entities, its attributes, and the relationships that occur between entities and draw entity relationship diagram (except question 10).

ii. Map the entity relationship diagram in to relational model.

iii. Write the sql statement of the relational model (ii).

1. Consider a MOVIE database in which data is recorded about the movie industry. The data requirements are summarized as follows:

• Each movie is identified by title and year of release. Each movie has a length in minutes. Each has a production company, and each is classified under one or more genres (such as horror, action, drama, and so forth). Each movie has one or more directors and one or more actors appear in it. Each movie also has a plot outline. Finally, each movie has zero or more quotable quotes, each of which is spoken by a particular actor appearing in the movie.
• Actors are identified by name and date of birth and appear in one or more movies. Each actor has a role in the movie.
• Directors are also identified by name and date of birth and direct one or more movies. It is possible for a director to act in a movie (including one that he or she may also direct).
• Production companies are identified by name and each has an address. A production company produces one or more movies.

Design an entity-relationship diagram for the movie database.

2. Consider a CONFERENCE_REVIEW database in which researchers submit their research papers for consideration. Reviews by reviewers are recorded for use in the paper selection process. The database system caters primarily to reviewers who record answers to evaluation questions for each paper they review and make recommendations regarding whether to accept or reject the paper. The data requirements are summarized as follows:

• Authors of papers are uniquely identified by e-mail id. First and last names are also recorded.
• Each paper is assigned a unique identifier by the system and is described by a title, abstract, and the name of the electronic file containing the paper.
• A paper may have multiple authors, but one of the authors is designated as the contact author.
• Reviewers of papers are uniquely identified by e-mail address. Each reviewer's first name, last name, phone number, affiliation, and topics of interest are also recorded.
• Each paper is assigned between two and four reviewers. A reviewer rates each paper assigned to him or her on a scale of 1 to 10 in four categories: technical merit, readability, originality, and relevance to the conference. Finally, each reviewer provides an overall recommendation regarding each paper.
• Each review contains two types of written comments: one to be seen by the review committee only and the other as feedback to the author(s).

Design an entity-relationship diagram for the CONFERENCE_REVIEW database.

3. Consider a MAIL_ORDER database in which employees take orders for parts from customers. The data requirements are summarized as follows:

• The mail order company has employees, each identified by a unique employee number, first and last name, and Zip Code.
• Each customer of the company is identified by a unique customer number, first and last name, and Zip Code.
• Each part sold by the company is identified by a unique part number, a part name, price, and quantity in stock.
• Each order placed by a customer is taken by an employee and is given a unique order number. Each order contains specified quantities of one or more parts. Each order has a date of receipt as well as an expected ship date. The actual ship date is also recorded.

Design an entity-relationship diagram for the mail order database.

4. Suppose that a UNIVERSITY database is needed to keep track of student enrollments in classes and students' final grades. The data requirements are summarized as follows:

• The university is organized into colleges (COLLEGE), and each college has a unique name (CName), a main office (COffice) and phone (CPhone), and a particular faculty member who is dean of the college. Each college administers a number of academic departments (DEPT). Each department has a unique name (DName), a unique code number (DCode), a main office (DOffice) and phone (DPhone), and a particular faculty member who chairs the department. We keep track of the start date (CStartDate) when that faculty member began chairing the department.
• A department offers a number of courses (COURSE), each of which has a unique course name (CoName), a unique code number (CCode), a course level (Level: this can be coded as 1 for freshman level, 2 for sophomore, 3 for junior, 4 for senior, 5 for MS level, and 6 for PhD level), a course credit hours (Credits), and a course description (CDesc). The database also keeps track of instructors (INSTRUCTOR); and each instructor has a unique identifier (Id), name (IName), office (IOffice), phone (IPhone), and rank (Rank); in addition, each instructor works for one primary academic department.
• The database will keep student data (STUDENT) and stores each student's name (SName, composed of first name (FName), middle name (MName), last name (LName)), student id (Sid, unique for every student), address (Addr), phone (Phone), major code (Major), and date of birth (DoB). A student is assigned to one primary academic department. It is required to keep track of the student's grades in each section the student has completed.
• Courses are offered as sections (SECTION). Each section is related to a single course and a single instructor and has a unique section identifier (SecId). A section also has a section number (SecNo: this is coded as 1, 2, 3, . . . for multiple sections offered during the same semester/year), semester (Sem), year (Year), classroom (CRoom: this is coded as a combination of building code (Bldg) and room number (RoomNo) within the building), and days/times (DaysTime: for example, 'MWF 9am-9.50am' or 'TR 3.30pm-5.20pm'- restricted to only allowed days/time values). (Note: The database will keep track of all the sections offered for the past several years, in addition to the current offerings. The SecId is unique for all sections, not just the sections for a particular semester.) The database keeps track of the students in each section, and the grade is recorded when available (this is a many-to-many relationship between students and sections). A section must have at least five students.

Design an entity-relationship diagram for a university database.

5. Design an ER diagram 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 U.S. STATE's Name (e.g., 'Texas', 'New York', 'California') and include the Region of the state (whose domain is {'Northeast', 'Midwest', 'Southeast', 'Southwest', 'West'}). 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', '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 also keeps track of how each congressperson voted on each bill (domain of Vote attribute is {'Yes', 'No', 'Abstain', 'Absent'}).

6. Consider a GRADE_BOOK database in which instructors within an academic department record points earned by individual students in their classes. The data requirements are summarized as follows:

• Each student is identified by a unique identifier, first and last name, and an e-mail address.
• Each instructor teaches certain courses each term. Each course is identified by a course number, a section number, and the term in which it is taught. For each course he or she teaches, the instructor specifies the minimum number of points required in order to earn letter grades A, B, C, D, and F. For example, 90 points for an A, 80 points for a B, 70 points for a C, and so forth.
• Students are enrolled in each course taught by the instructor.
• Each course has a number of grading components (such as midterm exam, final exam, project, and so forth). Each grading component has a maximum number of points (such as 100 or 50) and a weight (such as 20% or 10%). The weights of all the grading components of a course usually total 100.
• Finally, the instructor records the points earned by each student in each of the grading components in each of the courses. For example, student 1234 earns 84 points for the midterm exam grading component of the section 2 course CSc2310 in the fall term of 2009. The midterm exam grading component may have been defined to have a maximum of 100 points and a weight of 20% of the course grade.

Design an entity-relationship diagram for the grade book database.

7. Assume we have the following application that models soccer teams, the games they play, and the players in each team. In the design, we want to capture the following:

• We have a set of teams, each team has an ID (unique identifier), name, main stadium, and to which city this team belongs.
• Each team has many players, and each player belongs to one team. Each player has a number (unique identifier), name, DoB, start year, and shirt number that he uses.
• Teams play matches, in each match there is a host team and a guest team. The match takes place in the stadium of the host team.

For each match we need to keep track of the following:

• The date on which the game is played.
• The final result of the match.
• The players participated in the match. For each player, how many goals he scored, whether or not he took yellow card, and whether or not he took red card.
• During the match, one player may substitute another player. We want to capture this substitution and the time at which it took place.

Each match has exactly three referees. For each referee we have an ID (unique identifier), name, DoB, years of experience. One referee is the main referee and the other two are assistant referee.

Design an ER diagram to capture the above requirements. State any assumptions you have that affects your design.

8. UPS prides itself on having up-to-date information on the processing and current location of each shipped item. To do this, UPS relies on a company-wide information system. Shipped items are the heart of the UPS product tracking information system. Shipped items can be characterized by item number (unique), weight, dimensions, insurance amount, destination, and final delivery date. Shipped items are received into the UPS system at a single retail center. Retail centers are characterized by their type, uniqueID, and address. Shipped items make their way to their destination via one or more standard UPS transportation events (i.e., flights, truck deliveries). These transportation events are characterized by a unique scheduleNumber, a type (e.g, flight, truck), and a deliveryRoute.

Design an Entity Relationship diagram that captures this information about the UPS system.

9. The Prescriptions-R-X chain of pharmacies has offered to give you a free life-time supply of medicines if you design its database. Given the rising cost of health care, you agree. Here is the information that you gather.

• Patients are identified by SSN, and their names, addresses, and also ages.
• Doctors are identified by an SSN, for each doctor, the name, specialty and years of experience must be recorded.
• Each pharmaceutical company is identified by name and has a phone number.
• For each drug, the trade name and formula must be recorded. Each drug is sold by a given pharmaceutical company, and the trade name identifies a drug uniquely from among the products of that company. If a pharmaceutical company is deleted, you need not keep track of its products any longer.
• Each pharmacy has a name, address, and phone number.
• Every patient has a primary physician. Every doctor has at least one patient.
• Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another.
• Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. Each prescription has a date and a quantity associated with it. You can assume that if a doctor prescribes the same drug for the same patient more than once, only the last such prescription needs to be stored.
• Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceutical company can contract with several pharmaceutical companies. For each contract, you have to store a start date, and end date, and the text of the contract.
• Pharmacies appoint a supervisor for each contract. There must always a supervisor for each contract.

Design an Entity Relationship diagram that captures this information.

10. Describe the problems that arise in modeling entity relationship?