# Connexions

You are here: Home » Content » Exercise 1: ER Modelling

### Lenses

What is a lens?

#### Definition of a lens

##### Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

##### What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

##### Who can create a lens?

Any individual member, a community, or a respected organization.

##### What are tags?

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

#### Affiliated with (What does "Affiliated with" mean?)

This content is either by members of the organizations listed or about topics related to the organizations listed. Click each link to see a list of all content affiliated with the organization.
• VOCW

This module is included inLens: Vietnam OpenCourseWare's Lens
By: Vietnam OpenCourseWare

Click the "VOCW" link to see all content affiliated with them.

### Recently Viewed

This feature requires Javascript to be enabled.

# Exercise 1: ER Modelling

Module by: Nguyen Kim Anh. E-mail the author

Summary: This module contains practice exercise for ER Modelling.

## Exercise 1

Researchers work on different research projects, and the connection between them can be modelled by a WorksOn relationship. Consider the following two different ER diagrams to represent this situation.

Describe the different semantics suggested by each of these diagrams.

## Exercise 2

Draw an ER diagram for the following application from the manufacturing industry:

• Each supplier has a unique name.
• More than one supplier can be located in the same city.
• Each part has a unique part number.
• Each part has a colour.
• A supplier can supply more than one part.
• A part can be supplied by more than one supplier.
• A supplier can supply a fixed quantity of each part.

## Exercise 3

[Based on exercise 2.2 in Ramakrishnan& Gehrke] Consider a relationship Teaches between teachers and courses. For each situation described below, give an ER diagram that accurately models that situation:

• Teachers may teach the same course in several semesters, and each must be recorded
• Teachers may teach the same course in several semesters, but only the current offering needs to be recorded (assume this in the following parts)
• Every teacher must teach some course
• Every teacher teaches exactly one course
• Every teacher teaches exactly one course, and every course must be taught by some teacher
• A course may be taught jointly by a team of teachers

You may assume that the only attribute of interest for teachers is their staff number while for courses it is the course code. You may introduce any new attributes, entities and relationships that you think are necessary.

## Exercise 4

Give an ER design for a University administration database that records information about faculties, schools, lecturers, students, courses, classes, buildings, rooms, marks. The model needs to include:

• for each faculty, its name, its schools and its dean
• for each school, its name, the location of its school office, its head and its academic staff
• for each lecturer, their names, bithdate, position, staff number, school, office, the courses they have convened, and the classes they have run
• for each student, their names, birthdate, student number, degree enrolled in, courses studied, and marks for each course
• for each course, its code, its name, the session it was offered, its lecturer(s), its students, its classes
• for each class, what kind of class (lecture, tutorial, lab class, ...), its day and time (starting and finishing), who teaches it, which students attend it, where it's held
• for each building, its name and map reference
• for each room, its name, its capacity, type of room (office, lecture theatre, tutorial room, laboratory, ...) and the building where it is located

An assumption: staff and student numbers are unique over the union of the sets of staff and student numbers (i.e. each person has a unique identifying number within the University).

Another assumption: lecturers typically teach classes in the courses they convene; they may also teach classes in other courses; a given class is only taught by one lecturer.

State all other assumptions.

## Exercise 5

Give an ER design to model the following scenario :

• Patients are identified by an SSN, and their names, addresses and ages must be recorded.
• Doctors are identified by an SSN. For each doctor, the name, specialty and years of experience must be recorded.
• Each pharmacy has a name, address and phone number. A pharmacy must have a manager.
• A pharmacist is identified by an SSN, he/she can only work for one pharmacy. For each pharmacist, the name, qualification must be recorded.
• For each drug, the trade name and formula must be recorded.
• 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 between pharmacies.
• 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 quantity associated with it.

State all assumptions used in developing your data model.

## Content actions

PDF | EPUB (?)

### What is an EPUB file?

EPUB is an electronic book format that can be read on a variety of mobile devices.

My Favorites (?)

'My Favorites' is a special kind of lens which you can use to bookmark modules and collections. 'My Favorites' can only be seen by you, and collections saved in 'My Favorites' can remember the last module you were on. You need an account to use 'My Favorites'.

| A lens I own (?)

#### Definition of a lens

##### Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

##### What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

##### Who can create a lens?

Any individual member, a community, or a respected organization.

##### What are tags?

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

| External bookmarks