DOC

a1-2012-finalized

By Donna Rose,2014-11-14 23:30
5 views 0
a1-2012-finalized

    Assignment 1

    Question 1

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

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

     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. A grade report has a student, section, letter grade, and numeric grade (0, 1, 2, 3, 4 for F, D, C, B, A,

    respectively).

(a) Draw an Enterprise Data Model for this application.

    (b) Draw an ER diagram for this application.

    Note any unspecified requirements, and make appropriate assumptions to make the specification complete.

    Question 2 Consider Figure 1. Explain the meaning of the line that connects ORDER to INVOICE and the line that connects INVOICE to PAYMENT. What does this say about how Pine Valley Furniture Company does business with its customers?

    Figure 1

    1

Question 3

    Add a subtype discriminator for each of the supertypes shown in Figure 2. Show the discriminator values that assign instances to each subtype. Use the following subtype discriminator names and values: a. PERSON: Person_Type (Employee?, Alumnus?, Student?)

    b. EMPLOYEE: Employee_Type (Faculty, staff)

    c. STUDENT: Student_Type (Grad, Undergrad)

    Figure 2

Question 4

    A rental car agency classifies the vehicles it rents into four categories: compact, mid-size, full-size, and sport utility. The agency wants to record the following data for all vehicles: Vehicle_ID, Make, Model, Year, and Color. There are no unique attributes for any of the four classes of vehicle. The entity type vehicle has a relationship (named Tents) with a customer entity type. None of the four vehicle classes has a unique relationship with an entity type. Would you consider creating a supertype/ subtype relationship for this problem? Why or why not?

Question 5

    Design a database to keep track of information for an art museum. Assume that the following requirements were collected:

    ; The museum has a collection of ART_OBJECTS. Each ART_OBJECT has a unique Id_no, an Artist

    (if known), a Year (when it was created, if known), a Title, and a Description. The art objects are

    categorized in several ways, as discussed below.

    ; ART_OBJECTS are categorized based on their type. There are three main types: PAINTING,

    SCULPTUES, and STATUE, plus another type called OTHER to accommodate objects that do not

    fall into one of the three main types.

    2

    ; A PAINTING has a Paint_type (oil, watercolor, etc.), material on which it is Drawn_on (paper, canvas,

    wood, etc.), and Style (modern, abstract, etc.).

    ; A SCULPTURE or a statue has a Material from which it was created (wood, stone, etc.), Height,

    Weight, and Style.

    ; An art object in the OTHER category has a Type (print, photo, etc.) and Style. ; ART_OBJECTs are categorized as either PERMANENT_COLLECTION (objects that are owned by

    the museum) and BORROWED. Information captured about objects in the

    PERMANENT_COLLECTION includes Date_acquired, Status (on display, on loan, or stored), and

    Cost. Information captured about BORROWED objects includes the Collection from which it was

    borrowed, Date_borrowed, and Date_returned.

    ; Information describing the country or culture of Origin (Italian, Egyptian, American, Indian, and so

    forth) and Epoch (Renaissance, Modern, Ancient, and so forth) is captured for each ART_OBJECT. ; The museum keeps track of ARTIST information, if known: Name, DateBorn (if known), Date_died

    (if not living), Country_of_origin, Epoch, Main_style, and Description. The Name is assumed to be

    unique.

    ; Different EXHIBITIONS occur, each having a Name, Start_date, and End_date. EXHIBITIONS are

    related to all the art objects that were on display during the exhibition.

    ; Information is kept on other COLLECTIONS with which the museum interacts, including Name

    (unique), Type (museum, personal, etc.), Description, Address, Phone, and current Contact_person.

    An ER diagram is given below for this application. Transform the ER diagram to a relational model.

     ARTISTCOLLECTIONSNameNameDateBorn TypeDate_diedDescriptionCountry_of_originAddressEpochPhoneMain_styleContact_personDescription

    Creates Borrow from

    BORROWEDART_OBJECTSCollectionId_no“B”Own_type=Date_borrowedYeardDate_returned TitleDescription EXIHIBITIONShows“P”OriginNamePERMANENT_EpochStart_dateCOLLECTIONTypeEnd_dateDate_acquiredOwn_typeStatusCost

    Type =

    d

    “O”“P”“SC”“ST”

    SCULPTURESSTATUE PAINTINGMaterialMaterialOTHERPaint_typeHeightHeightTypeDrawn_onWeight Weight StyleStyle Style Style

    3

Report this document

For any questions or suggestions please email
cust-service@docsford.com