Solved–Lab 1: ER Diagram– Solution

Description

1.1    What is an ER Diagram

 

An Entity Relationship Diagram (ER-Diagram) is a visual representation of di erent entities within a system and how they relate to each other.

 

1.2    ER Diagram Conventions

 

Component                Description

 

An entity is denoted by a rectangle. An entity is an object that exists and is distinguishable from other objects. An entity may be concrete (a person or a book, for example) or abstract (like a holiday or a disease or a concept).

 

An attribute is denoted by a oval. Each entity is represented by a set of attributes. (e.g. name, SSN, Phone Number for ‘customer’ entity.)

 

A relationship between two or more entities is denote by a dia-mond.

 

Table 1: ER Diagram

 

 

An entity should have a unique attribute which uniquely identi es each entry in the entity set. These are called strong entities. For example, if we have a entity student with attributes SID, name, and phone. Then this is a strong entity because SID can uniquely identify each item in the entity. An attribute that uniquely identi es an item in the entity is called a primary key, and is denoted using an underline.

 

There are some entities exists some entity type for which key attribute cant be de ned. These are called Weak Entity type. As the weak entities do not have any primary key, they cannot be identi ed on their own, so they depend on some other entity (known as owner entity). Weak entity types have partial keys. Partial Keys are set of attributes with the help of which the tuples of the weak entities can be distinguished and identi ed. Weak entities are represented with double rectangular box in the ER Diagram and the identifying relationships are represented with double diamond. Partial Key attributes are represented with dotted lines.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 2: Weak Entity

Figure 1: Strong Entity

 

 

 

 

 

 

1

 

Next, we shall de ne cardinality constraints between entities.

 

Component                                     Description

 

 

In a one-one relationship, each entity of either entity set is related to at most one entity of the other set.

 

 

 

In a many-many relationship, an entity of either set can be connected to many entities of the other set.

 

 

 

A many-to-one relationship means that each entity of the sec-ond set is connected to zero, one or many entities of the rst set.

 

 

 

A one-to-many relationship means that each entity of the rst set is connected to zero, one or many entities of the second set.

 

 

 

Relationships can also be denoted as optional or mandatory. This denotes that we have a mandatory one-to-one relationship.

 

 

 

Relationships can also be denoted as optional or mandatory. This denotes that we have a optional one-to-one relationship.

 

 

 

Relationships can also be denoted as optional or mandatory. This denotes that we have a optional many-to-many relationship.

 

 

 

Relationships can also be denoted as optional or mandatory. This denotes that we have a mandatory many-to-many relationship.

 

 

Table 2: ER Diagram Relationships

 

 

 

1.3    Lab Goals

 

The goal of this lab is to practice the ER-modeling techniques. You need to draw the ER diagrams for each exercise. You can use https://erdplus.com/ to generate the ER Diagrams. This tool allows you to export the diagram in PDF or image format. Alternatively, you can use ‘Dia Diagram Editor’ (choose the ‘ER’ catalog) which is available on lab machines in Applications-Graphics menu, or another diagram editing online tool, like https://www.draw.io)

 

For each given problem:

 

Work individually to draw out the ER-Diagram on paper

 

 

 

 

2

 

When you have a draft, discuss with your lab partner. Does your design agree? Do you have suggestions for each other? Its okay to share thoughts and have a discussion.

 

Once you are con dent with your design, go ahead and draw your diagram using a graphics tool Finally, upload your solution to iLearn as a PDF le or any image format.

 

1.4    Part 1 – University

 

Design and draw an ER diagram that captures the information about the university. Use only the basic ER model here, that is include only entities, relationships and attributes. Dont forget to indicate any key and participation constraints. Consider the following information about a university database:

 

Professors have an SSN, a name, an age, a rank and a research specialty.

 

Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date and a budget. Graduate students have an SSN, a name, an age and a degree program (e.g., M.S. or Ph.D.).

 

Each project is managed by one professor (known as the projects principal investigator).

 

Each project is worked on by one or more professors (known as the projects co-investigators). Professors can manage and/or work on multiple projects.

 

Each project is worked on by one or more graduate students (known as the projects research assistants).

 

When graduate students work on a project, a professor must supervise their work on the project. Graduate students can work on multiple projects, in which case they will have a (potentially di erent) supervisor for each one.

 

Departments have a department number, a department name and a main o ce. Departments have a professor (known as the chairman) who runs the department.

 

Professors work in one or more departments and for each depart- ment that they work in, a time percentage is associated with their job.

 

Graduate students have one major department in which they are working on their degree.

 

Each graduate student has another, more senior graduate student (known as a student advisor) who advises him or her on what courses to take.

 

1.5    Part 2 – Notown

 

Design and draw an ER diagram for your schema. Be sure to indicate all key and cardinality constraints and any assumptions that you make. Identify any constraints that you are unable to capture in the ER diagram and brie y explain why you could not express them. The following information describes the situation that the Notown database must model. Notown Records has decided to store information about musicians who perform on its albums (as well as other company data) in a database.

 

Each musician that records at Notown has an SSN, a name, an address and a phone number. Poorly paid musicians often share the same address, and no address has more than one phone.

 

Each instrument that is used in songs recorded at Notown has a name (e.g., guitar, synthesizer, ute) and a musical key (e.g., C, E).

 

Each album that is recorded on the Notown label has a title, a copyright date, a format (e.g., CD or MC), and an album identi er.

 

Each song recorded at Notown has a title and an author.

 

Each musician may play several instruments, and a given instrument may be played by several musicians.

 

 

 

3

 

Each album has a number of songs on it, but no song may appear on more than one album.

 

Each song is performed by one or more musicians, and a musician may perform a number of songs.

 

Each album has exactly one musician who acts as its producer. A musician may produce several albums, of course.

 

1.6    Turn-in

 

For each problem, export  gure as a PDF or JPEG    le and upload to iLearn under Lab 1 assignment.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4