# 代做express | 作业project | lab | database代写 – Assignment 1 : ER models and EER models

### Assignment 1 : ER models and EER models

Due Monday by 11:59pm Points 10 Submitting a file upload Available Feb 27 at 12am – Mar 6 at 11:59pm

``````Start Assignment
``````

Note: Due to the complexity of E-R diagram drawing, I will accept hand-drawing diagrams scanned. However, I strongly recommend you use computer aided tool for diagrams. Microsoft Visio, Adobe Illustrator and even PowerPoint are useful tools for drawing (Ive attached a sample slide with the building blocks/annotations of the EER model weve learnt so far, in the files section of Camino page.). If you dont have any of these tools, you can also try: https://www.canva.com/ (https://www.canva.com/).

1. For each of the following pairs of related entities, indicate whether (under typical circumstances) there is a one-to-many or a many-to-many relationship. Then, using the basic E-R model introduced in the class, draw a diagram for each of the relationships. 1. STUDENT and COURSE (students register for courses) 2. BOOK and BOOK COPY (books have copies) 3. COURSE and SECTION (courses have sections) 4. SECTION and ROOM (sections are scheduled in rooms) 5. INSTRUCTOR and COURSE
2. As shown in the figure below, a cellular operator needs a database to keep track of its customers, their subscription plans, and the handsets (mobile phones) that they are using. The E-R diagram illustrates the key entities of interest to the operator and the relationships between them. Based on the figure, answer the following questions and explain the rationale for your response. For each question, identify the element(s) in the E-R diagram that you used to determine your answer. 1. Can a customer have an unlimited number of plans? 2. Can a customer exist without a plan? 3. Is it possible to create a plan without knowing who the customer is? 4. Does the operator want to limit the types of handsets that can be linked to a specific plan type? 5. Is it possible to maintain data regarding a handset without connecting it to a plan? 6. Can a handset be associated with multiple plans? 7. Assume a handset type exists that can utilize multiple operating systems. Could this situation be accommodated within the model included in Figure 2-24? 8. Is the company able to track a manufacturer without maintaining information about its handsets?
1. Can the same operating system be used on multiple handset types?
2. There are two relationships between Customer and Plan. Explain how they differ.
3. Characterize the degree and the cardinalities of the relationship that connects Customer to itself. Explain its meaning.
4. Is it possible to link a handset to a specific customer in a plan with multiple customers?
5. Can the company track a handset without identifying its operating system? Please explain.
6. For each of the descriptions below, perform the following tasks:
7. Identify the degree and cardinalities of the relationship.
8. express the relationships in each description graphically with an E-R diagram.

(Hint: it may be much easier that you come up with the whole E-R diagram with entities and relationships drawn preliminarily, and then identify the degree and cardinalities of the relationship in detail.)

1. A book is identified by its ISBN number, and it has a title, a price, and a date of publication. It is published by a publisher, which has its own ID number and a name. Each book has exactly one publisher, but one publisher typically publishes multiple books over time.
2. A book (see 3a) is written by one or multiple authors. Each author is identified by an author number and has a name and date of birth. Each author has either one or multiple books; in addition, occasionally data are needed regarding prospective authors who have not yet published any books.
3. In the context specified in 3a and 3b, better information is needed regarding the relationship between a book and its authors. Specifically, it is important to record the percentage of the
``````royalties that belongs to a specific author, whether or not a specific author is a lead author of the
book, and each authors position in the sequence of the books authors.
``````
1. A book (see 3a) can be part of a series, which is also identified as a book and has its own ISBN number. One book can belong to several sets, and a set consists of at least one but potentially many books.
2. A company has a number of employees. The attributes of EMPLOYEE include Employee ID (identifier), Name, Address, and Birthdate. The company also has several projects. Attributes of project include Project ID (identifier), Project Name, and Start Date. Each employee may be assigned to one or more projects or may not be assigned to a project. A project must have at least one employee assigned and may have any number of employees assigned. An employees billing rate may vary by project, and the company wishes to record the applicable billing rate (Billing Rate) for each employee when assigned to a particular project.
3. Draw an ERD for the above situation. (If you believe that you need to make additional assumptions, clearly state them for each situation.)
4. Do the attribute names in this description follow the guidelines for naming attributes? If not, suggest better names.
5. Do you have any associative entities on your ERD? If so, what are the identifiers for those associative entities?
6. Does your ERD allow a project to be created before it has any employees assigned to it? Explain.
7. How would you change your ERD if the Billing Rate could change in the middle of a project?
8. Add a subtype discriminator for each of the supertypes shown in the figure below from the class material. Show the discriminator values that assign instances to each subtype. Use the following subtype discriminator names and values:
9. PERSON: Person Type (Employee? Alumnus? Student?)
10. EMPLOYEE: Employee Type (Faculty, Staff)
1. Develop an EER model for the following situation using the traditional EER notation or the subtypes inside supertypes notation. (Hint: dont be overwhelmed by the rules here. Actually, the more rules you have, the easier you can develop the model. Always start with identifying the entities.)

An international school of technology has hired you to create a database management system to assist in scheduling classes. After several interviews with the president, you have come up with the following list of entities, attributes, and initial business rules:

``````Room is identified by Building ID and Room No and also has a Capacity. A room can be either a lab
or a classroom. If it is a classroom, it has an additional attribute called Board Type.
Media is identified by MType ID and has attributes of Media Type and Type Description. Note: Here
we are tracking type of media (such as a VCR, projector, etc.), not the individual piece of equipment.
Tracking of equipment is outside the scope of this project.
Computer is identified by CType ID and has attributes Computer Type, Type Description, Disk
Capacity, and Processor Speed. Please note: As with Media Type, we are tracking only the type of
computer, not an individual computer. You can think of this as a class of computers (e.g., Macbook
Pro 2022).
Instructor has identifier Emp ID and has attributes Name, Rank, and Office Phone.
Timeslot has identifier TSIS and has attributes Day of Week, Start Time, and End Time.
Course has identifier Course ID and has attributes Course Description and Credits. Courses can
have one, none, or many prerequisites. Courses also have one or more sections.
Section has identifier Section ID and attribute Enrollment Limit.
``````