Chapter II

Conceptual Model
Entity Association(/Relationship) Model
Practical Process
Steps in the data modeling process
• Plan project.
• Determine requirements.
• Specify entities.
• Specify relationships.
• Determine identifiers.
• Specify attributes.
• Specify domains.
• Validate model.

2
How to build a database

• To build a database, you have to

1. Construct a conceptual schema, modeled in entities and associations

2. Transform the E/A (E/R) model into relational schema

3. Implement via a DBMS

3
Conceptual Modeling
Conceptual Model (Entity Association/Relationship model)
• Also known as Entity-Association Diagrams (EAD)
• Introduced in 1976 by Peter Chen
• Graphical representation
• Top-Down-Approach for modeling

– Entities and Attributes

– Relationships
– Constraints

4
The Entity / Association Model - Basic Concepts
• The E / A model is a graphical formalism for data modeling
• Success due to:
• graphic language
• Represent the entity by a rectangle
• represent the association by a lozenge or circle
• simple concepts:
• Things (objects) -> entities
• links between things (objects) -> association/relation
• grouping things of the same nature:
• feature classes, association classes.
5
Purpose of Entities
• Knowing how to organize and classify data makes it possible to draw useful
conclusions about seemingly random facts
• Our technology-rich world produces vast quantities of facts in need of structure and
order
• It is important to learn about entities because they are the things about which we
store data
• For example;
• A school needs to store data about (as a minimum):
STUDENTs, TEACHERs, COURSEs, ROOMs, GRADEs

6
Specifying your entities
• An entity is something that the users want to track; something the users want to keep
data about

• Entities
• Can be physical things or logical concepts: an object, an event, a place, a person,
..., a thing,
• Are identifiable; you can tell one from another.
Entiy Name
• Usually a noun
• Entity class formalism: graphical representation

7
Entities

• Example: MOVIES, CINEMA, ACTORS, REALIZERS

MOVIE Actors

CINEMA

Realizers

8
Entities and Instances
• An instance is a single occurrence of an entity

9
Instances vs Entity
• Is DOG an instance or an entity?

• It depends:
• If we consider many different kinds of animals, it makes sense to think of the entity ANIMAL
to include instances DOG,CAT, HORSE and so on
• But what if we run a dog-breeding business? We will need to keep data on may different
breeds of dog, but not on other species of animal
• For a dog-breeder, it is more natural to think of an entity DOG to include instances TERRIER,
POODLE,LBRADOR and so on

10
Specifying Association/Relationships

• An association : It is a link between two or more entities (a verb)

• Examples:
• Nicole K played in "Human Stain"
• Tannenbaum wrote the book Computer Networks

• Includes:
• Relationship type.
• Name of the relationships.

11
Properties = Attributes
• Elementary data that we perceive on an entity or an association

• Example:
• Surname, first name for the entity ACTORS
• Title and Director for FILMS
• Name and Address For CINEMA

• They provide more specific information of an entity

• Help you distinguish between once instance and another by providing greater
detail for the entity
12
What is an Attribute

• Like an entity, an attribute represents something of significance to the business

• An attribute is a specific piece of information that helps

• Describe an entity
• Quantify an entity
• Qualify Classify an entity
• Specify an entity

Extended attributes: composite, multi-valued, derived

13
Attributes

• Attributes have values. An attribute value can be a number, a character string, a date,
an image, a sound,etc.
• These are called “data types” or “formats”
• Every attribute stores one piece of data of one specific data type.

14
Attributes
• What is the data type of each attribute in CUSTOMER
• For example:
• Family name is a character string, attributes are single-valued
• Each attribute can have only one value (at any point in time) for each instance of the entity

15
Attributes
• Some attributes (such as age) have values that constantly change
• These are called volatile attributes
• Other attributes (such as order date) will rarely change, if eve
• These are nonvolatile attributes
• If given a choice, select the nonvolatile attribute
• For example, use birth date instead of age

16
Attributes
• Some attributes must contain a value, these are mandatory attributes

• For example: in most businesses that track personal information, name is required

• Other attributes may either contain a value or be left null- these are optional attributes

• For example: cell phone number is often optional except in mobile or wireless applications

17
 Type of Attributes
• Atomic Attributes
• Basic, single-valued attributes
• Composite Attributes
• Attributes as structured data types
• Can be represented as a hierarchy
• Derived Attributes :Attributes derived from other data
• Noted with dashed circle
• Examples: Number of employees in dep, employee age, employee
yearly salary
• Multi-valued Attributes :
• Attributes with list of homogeneous entries
• Noted with double circle
Database Design 18
Attributes: Example
• Email address could be a mandatory attribute for EMPLOYEE in an email
application, but an optional attribute for CUSTOMER in an online
catalog

• If we were to model a HR system, we would have an entity to store data

for each worker called EMPLOYEE

• What attributes does EMPLOYEE have?

• Give one or two examples of the values that each EMPLOYEE attribute
might contain
19
Example

Database Design 20
Properties
• For associations:
• BE CAREFUL, the properties/attribute that we put in an
association/relation must obligatorily connect the entities

• For example, the Grade connects the student and the professor

23
Identifiers
• Think about all the students in the classroom

• Each student is described by several attributes

• Which attributes allow you to pick a single student from the rest of the class?

• That is the student’s UID

24
Determining Identifier
• Identifier is an attribute or group of attributes that uniquely identifies an entity
instance.
• One identifier (Uniqueness)
• We prefer the shortest, most natural identifier (no order form, no student, ...)
• The identifier is not variable in time

25
Identifier

Objective:
• Each occurrence must be uniquely and unambiguously identifiable, to
be distinguished from all others

Identifier:
• Property (attribute) or group of properties whose value
unambiguously identifies an entity or a link of a class (identifier =
primary key).

26
Weak entities
• A weak entity is one that needs another entity to be defined.
• Weak entities are entities whose primary key is :
• a subset of a primary key of another entity :
• composed of the primary key of another entity and another attribute (which
belongs to the weak entity)
• noted with a double rectangle.

28
Example
• 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.

30
Example
• A Company is organized into departments.
• Each department has a number and an employee who manages the department.
• We keep track of the start date when that employee started managing the department.
• A department may have several locations.
• A department controls a number of projects.
• Each of which has a name, a number and a single location.
• We store each employee’s name, Id number, address, salary, and birth date.
• An employee is assigned to one department, but may work on several projects, which are not
necessarily controlled, by the same department.
• We keep track of the number of hours per week that an employee works on each project.
• We also keep track of the direct supervisor of each employee.
• We keep track of the dependents of each employee.
• We keep each dependent's name, birth date and relationship to the employee.

Phase 1 of database design process.. (i.e. Requirements Collection and Analysis )

31
Conceptual Design
• All the requirements collected at Phase 1 are analyzed to create a Conceptual Schema.

• This process is called the Conceptual Design.

• We identify the entities, their attributes, relationships and constraints(business rules).

• The conceptual schema is used as a reference to ensure that all user’s data
requirements are met, and the requirements do not include any conflicts.

32
Conceptual Design

33
Detailed Conceptual Design

34
Detailed Conceptual Design

35
Entities and Attributes
• What are the attributes of a monitoring site?

Attributes Values
• Site Name: Little Bear River near Wellsville
• Site Code: USU-LBR-Wellsville
• Latitude: 41.643457
• Longitude: -111.917649
• Elevation: 1365 m
• State: Utah
• County: Cache
• Description: Attached to SR101 bridge.
• Site Type: Stream

All instances of an entity have the

Entity = Monitoring Site
same attributes
Entity Notation

Entity Name

Attributes Data Type

Entity
Multiplicity : cardinalities of associations/relationships

• An association makes it possible to link one or more entities.

• Role determines how entities are linked
• Several types of association : 1:1 (one-to-one) , 1:N (one-to-many) , N:1 (many-
to-one) [ Symmetric to 1:N], M:N (many-to-many)

38
Relationships

39
One to Many

40
One to Many

41
One to Many

42
One To One Relationship

43
Many to Many

44
Many to Many

45
One to One

46
Cardinality And Multiplicity

Each association depicts the complexity or degree of each relationship, and this is called
cardinality.
• Cardinality defines the number of occurrences of one entity for a single occurrence
of the related entity.
• Because all relationships are bi-directional, cardinality must be defined in both
directions for every relationship.
• A multiplicity is made up of a lower and an upper cardinality.
• A cardinality is how many elements are in a set while a multiplicity tells you the
minimum and maximum allowed members of the set.

47
Multiplicity: Cardinality of a relationship
• Two numbers (min, max) representing the minimum number of times and the
maximum number of times an entity participates in an association.

• The possible values are : (0,1), (1,1); (0,N), (1,N)

48
Multiplicity: Cardinality of a relationship

• Min: Corresponds to the answer to the question: how many times at

least one entity of A is connected to a B entity

• Max: corresponds to the answer to the question: how many times at

most an entity of A is connected to a B entity

• Warning!! these questions must be asked in both directions from A

to B and from B to A.

49
Relationship Notation
• Multiple notation systems are used
• Each software program is a little different
• Most common is “Crows Foot” and Chen Notation
Crows Foot Chen Notation
0 .. m
1 .. m
1 .. 1
0 .. 1
Relationship Cardinality and Participation

• Cardinality: maximum number of times an instance in one entity can be

associated with instances in the related entity
• Participation: minimum number of times an instance in one entity can be
associated with instances in the related entity
Cardinality
• Maximum number of times an instance of an entity can be associated
with instances of a related entity
• Can ONLY have values of 1 or many
• Located closest to the entity in Crows Foot notation
 Participation

• Minimum number of times an instance in one entity can be

associated with instances in the related entity
• Indicates whether an instance of an entity MUST participate in
the relationship
• Can ONLY have the values of 0 or 1
Mapping Cardinality and Participation
Apple Grows_on AppleTree

A1 R1
T1
A2 R2
T2
A3 R3

A4 R4
T3
A5 R5

A6 R6
Reading Cardinality and Participation
Relationship Examples

Left to Right: A site has 0 or more time series of data.

Right to Left: A time series is measured at 1 and only 1 site.

Left to Right: A variable has 0 or more time series of data.

Right to Left: A time series can have 1 and only 1 variable.
Relationship Examples

Left to Right: A TimeSeries has 1 or more Observations.

Right to Left: An Observation belongs to 1 and only 1
TimeSeries.

Left to Right: An Observation has 0 or more Qualifiers.

Right to Left: A Qualifier describes 0 or more Observations.
Notations

58
Relationship Notation
• Multiple notation systems are used
• Each software program is a little different
• Oracle use a variation of “Crows Foot” developed by Richard Barker
Crows Foot Chen Notation
0 .. m
1 .. m
1 .. 1
0 .. 1
Types of Binary Relationship
Crow's foot

Database Design 60
Modeling cardinality and relationship

Database Design 61
In our written exercises we will use Chen Notation

Database Design 62
 0 to 1

• 0,1 : an entity of A can be connected to none or a single entity of B.

63
1 to 1

1,1 : an entity of A can be connected to a single entity of B.

64
0 to many

• From 0 to many (0, N): an entity of A can be connected to none or more of

B ...

65
1 to many

• From 1 to several (1, N): an entity of A can be connected to one or more

entities of B ...
1,N

66
Example

We want to model a simple university database

• In our database, we have students. They have a name, a registration number, and a
course of study.
• The university offers lectures. Each lecture may be part of some course of study in a
certain semester. Lectures may have other lectures as prerequisites. They have a title,
provide a specific number of credits and have an unique ID
• Each year, some of these lectures are offered by a professor at a certain day at a fixed
time in a specific room. Students may register for that lecture.
• Professors have a name and are member of a specific department.

67
How to start? What to do?
• Find the basic entity types
• Find the attributes of entities
• Decide to which entity an attribute should be assigned
• Which attributes are key attributes?
• Some attributes are better modeled as own entities, which ones?
• Define the relationship types
• Which role do entities play?
• Do relationships require additional entity types?
• Are the relationships total? Identifying? Are weak entities involved?
• What are the cardinalities of the relationship type?
68
Validating Model

• A data model is wrong if it does not accurately reflect the ways the
users think about their world.
• Data models are validated through a series of reviews
• Normally, a team review is followed by user reviews.
• E-A model as well as prototypes of forms and reports may be used
to communicate to users features of the data model.

69
Association n-dimension(n=2):Binary Relationship

Binary association relates two typed entities.

70
Association n-dimension (n=3):Ternary Relationship(n-ary)

• ternary association relates 3 typed entities (3 entities)

Multiplicity
• 1 Project and 1 Supplier supply P parts
• 1 Project and 1 Part supplied by N suppliers
• 1 Supplier and 1 Part supply for M projects
Example: A Typical Relationship Set

Bar Drinker Beer

Joe’s Bar Ann Miller
Sue’s Bar Ann Bud
Sue’s Bar Ann Pete’s Ale
Joe’s Bar Bob Bud
• Build the E/A model Joe’s Bar Bob Miller
Joe’s Bar Cal Miller
Sue’s Bar Cal Bud Lite

72
Example: Ternary Relationship
name addr name manf

license Bars Beers

Preferences

Drinkers

name addr
73
Recursive Relationships
• Recursive relationships are relations between entities of the same type
• Use roles to differentiate cardinalities

Database Design 74
Association n-dimensions(n=1): Unary Relationship/Association

75
Details on the occurrences of association

• Definition:
An association occurrence is an occurrence of each of the entities involved

76
Example
price name category

Start Year
name

makes Company
Product

Stock price

Product-name Product-Category Company-name Starting-year

gizmo gadgets gizmoWorks 1963

(watch out for attribute name conflicts)

Details on the occurrences of association
Important rule
There is only one instance of association connecting the same entity
occurrences. id
code

Student Course grade 0,n enrolls

0,n
Student Course
E2 P1 12
E2 P1 13 !!!!!! grade

This model is correct only if you manage a single score per student at a
Course (for example, the average)
Or →

78
 Details on the occurrences of association

Important rule (continued)

In the case of this model, it is possible to suggest, for example, a ternary association
grade

0,n enrolls 0,n

Student Course

0,n

date
Date

Example: on a given date, for a course., several students may have same grade

79
Relationship set as an entity set: Aggregation
name
ssn lot

• Used when we have to model a

Employees
relationship involving (entity
sets with their association) a
relationship set. Monitors until
• Aggregation allows us to treat a
Aggregation
relationship set as an entity set
for purposes of participation in started_on
dname
(other) relationships. pid pbudget did budget
• Monitors mapped to table like any
other relationship set. Projects Sponsors Departments
Example 1

• Draw the E/R Diagram for the following information:

• Students
• Have an Id, Name, Login, Age, GPA
• Courses
• Have an Id, Name, Credit Hours
• Students enroll in courses
• Receive a grade
Example 1 Answer

Name Login Age Id Name Credit

Id GPA
Students Courses

• Cardinalities?

Enrolled_In

Grade
Example 2

• Draw the E/A Diagram for the following information:

• Patients
• Name, Address, Phone #, Age
• Drugs
• Name, Manufacturer , Expiration Date
• Patients are prescribed drugs
• Dosage, # Days

83
Example 2 Answer

Name Addr Phone Name Manuf Exp

Age
Patients Drug

Prescribed

Dosage #days

84
Example

• Suppose a set of people and a set of cars.

• A person is characterized by a number that identifies him and by cars of which he is
the sole owner
• A car has a license plate number, a mark and a date of release.

85
Solution
Release
Date Model
Plate
no
Numbe

Has
Person Car
has 1-1
1-N

86
Practice EA Diagram
Suppose you are given the following requirements for a simple database for the National Hockey
League (NHL):
· the NHL has many teams,
· each team has a name, a city, a coach, a captain, and a set of players,
· each player belongs to only one team,
· each player has a name, a position (such as left wing or goalie), a skill level, and a set of injury
records,
· a team captain is also a player,
· a game is played between two teams (referred to as host_team and guest_team) and has a date
(such as May 11th, 1999) and a score (such as 4 to 2).
Draw a clean and concise EA diagram for the NHL database
List your assumptions and clearly indicate the cardinality mappings
as well as any role indicators in your EA diagram.
87
using the Chen notation

88
Constraints

• What are they?

• Represent the semantics of the domain being modeled.
• Restrict the set of possible database states
• Why do we want to specify the constraints?
• Useful information to application programmers. They can write programs to prevent constraints
violation
• constraint -- acct balance should not fall below 0 dollars
• programmer writing code for debit application should check at the end if the account gets overdrawn!
• DBMS might enforce specified constraints directly making task of application writer easier
• If DBMS guarantees that account does not get overdrawn, then debit application programmer need not
worry about checking for overdrawn account condition.

89
Integrity constraints

• Integrity constraints are a set of rules.

• It is used to maintain the quality of information.

• Integrity constraints ensure that the data insertion, updating, and other processes
have to be performed in such a way that data integrity is not affected.

90
Specifying Constraints in Data Models

• EA model
• domain and key constraints over entities
• participation and cardinality constraints over relationships

• Relational Model
• domain constraints, entity identity, key constraint, functional dependencies --
generalization of key constraints, referential integrity, inclusion dependencies --
generalization of referential integrity.

91
Domain Constraint

• In the schema, every attribute is declared to have a type --- integer, float, date,
Boolean, string, etc.

• An insertion request can violate the domain constraint.

• DBMS can check if insertion violates domain constraint and reject the insertion.

92
Key Constraint
• Each relation has a primary key.
• Superkey:
• Set of attributes such that if two tuples agree on those attributes, then they agree on all
the attributes of the relation
• Note: the set of all the attributes of a relation is always a superkey.
• Candidate key:
• Superkey is reduced to the minimum number of columns required to uniquely
identify each row.
• Primary key:
• one of the candidate keys

93
Disallowing Null Values

• Some fields of a relation are too important to contain null values.

• Example:
• in sales(customer, salesman, date, amount, saleID) we may not want ‘customer’ to
contain a null value.

94
Entity Integrity Constraint

• A primary key must not contain a null value.

• Else it may not be possible to identify some tuples.

• For Example, if more than one tuple has a null value in its primary key, we may not be
able to distinguish them .

95
Foreign Key and Referential Integrity Constraint
• Consider following 2 relation schemas:
• R1(A1, A2, …An) and R2(B1, B2, … Bm)

• Let PK be subset of {A1, …,An} be primary key of R1

• A set of attributes FK is a foreign key of R2 if:

• attributes in FK have same domain as the attributes in PK
• For all tuples t2 in R2, there exists a tuple t1 in R1 such that t2[FK] = t1[PK].

• A referential integrity constraint from attributes FK of R2 to R1 means that FK is a

foreign that refers to the primary key of R1.
96
Example of Referential Integrity
• student-grades
student C# Semester grade
Susan CS101 1-91 A
Jane CS101 1-91 B

• LegalGrades
Grade
A we will have a referential integrity
B constraint stating that
C every value of student-grades.grade
D must also be a value of
F LegalGrades.grade
97
The concept of integrity constraint
• Static:
• They must be constantly checked:
• Start_date <End_date
• an employee's salary must be less than her manager’s

• Dynamics:
• Validated with each update of the data:
• Loan of a book to a reader, provided that the total number of borrowed books is less than or equal to 3

• References:
• Linked to associations, check the existence of an occurrence of the entity concerned
• You can not create an order for a supplier who does not exist

98
Inclusion Dependencies
• Generalization of referential integrity constraint.

• Inclusion dependency R1[A1,...,An]  R2 [B1,...,Bn] means that the values in the

first relation R1 refer to the values in the second relation

• Formally, R1[A1,...,An]  R2 [B1,...,Bn] if the following holds:

• for all t1 in R1, there exists a t2 in R2 such that t1[A1, …, An] = t2[B1, …, Bn]

• Notice that referential integrity constraint is an inclusion dependency in which {B1,

.. Bn} is the primary key of R2.

99
Example
Here, by the constraint of inclusion, it is specified that all couples (software,
department) extracted from all the triplets (software, department, server) of the
association Installation are obligatorily included in all couples (software, department
) of the association buy.

Software Server

Department
buy
Constraints [exclusion]

An exclusion constraint of an association 1 Vs an association 2 expresses the fact that

the entities participating in the association 1 can not participate in the association 2.

Rent

Software Department

buy

101
Data Modification and Integrity Constraints
• Modification, insertion and deletion requests can lead to violations of integrity
constraints.
• Key constraint, entity identity, null value constraint, referential integrity, inclusion dependencies,
functional dependencies, multivalued dependencies.

• must check for violation at end of each operation and suitably allow or disallow
operation.

• Impact of data modification on inclusion dependencies can be sometimes tricky!

102
Example
• Relations
• CourseOfferings(C#, semester, instructor)
• Takes(S#, C#, semester, grade)

• Referential Integrity Constraint:

• Takes(C#,semester)  CourseOffering(C#,semester)

• Consider canceling a course.

• Delete from courseOfferings where c# = “CS101” AND Semester = “2-91”;

• What should happen to tuples in Takes that refer to CS101 and semester 2-91??
Example

• We want to represent information about products in a database.

• Each product has a description, a price and a supplier.

• Suppliers have addresses, phone number, and name.

• Each address is made up of a street address, a city, and a postcode.

Entity Relationship Modelling

Example - E/R Diagram
Price

Description Product

Street address

Name Supplier Address City

Phone number Postcode

Entity Relationship Modelling

Example - E/R Diagram
Price

Description Product

Has A Street address

Name Supplier Has A Address City

Phone number Postcode

Entity Relationship Modelling

Exercise 2.1
Problem
• A company database needs to store information about employees (identified by ssn,
with salary and phone as attributes), departments (identified by dno, with dname and
budget as attributes), and children of employees (with name and age as attributes).

• Employees work in departments; each department is managed by an employee; a child

must be identified uniquely by name when the parent (who is an employee; assume
that only one parent works for the company) is known. We are not interested in
information about a child once the parent leaves the company.

• Draw an ER diagram that captures this information.

Exercise 2.1
dname
salary
phone dno
ssn budget

Employees Departments
Manages

Cardinalities?
Dependent
Works_In

Child

name age
 Exercise 2.2
• Although you always wanted to be an artist, you ended up being an expert on databases because you love to
cook data and you somehow confused database with data baste. Your old love is still there, however, so you
set up a database company, ArtBase, that builds a product for art galleries. The core of this product is a
database with a schema that captures all the information that galleries need to maintain.
• Galleries keep information about artists, their names (which are unique), birthplaces, age, and style of art.
For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting,
lithograph, sculpture, photograph), and its price must be stored. Pieces of artwork are also classified into
groups of various kinds, for example, portraits, still lifes, works by Picasso, or works of the 19th century; a
given piece may belong to more than one group.
• Each group is identified by a name (like those just given) that describes the group. Finally, galleries keep
information about customers. For each customer, galleries keep that person’s unique name, address, total
amount of dollars spent in the gallery (very important!), and the artists and groups of art that the customer
tends to like.
• Draw the ER diagram for the database.
Exercise 2.2
year type
name title
price

Like_Group Group Classify Artwork

Multiplicity?
Customer Like_Artist Artist Paints

address style
cust_id name

amount birthplace age

name
Exercise 2.3
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 schedule Number, a type (e.g,
flight, truck), and a delivery Route.
Build the conceptual diagram that captures this information about the UPS system.
Be certain to indicate identifiers and cardinality constraints.
111
Solution

112
Exercise 2.4

• Construct "entity-association" schemas for the following statements

• The different departments of a company contains employees.
• An employee is described by his number (unique in the company), his name, his rank and
the department in which he works.
• A department is described by its number in the company and its location.
• A department is headed by a director who must be one of his employees.

113
Exercise 2.6 : Cardinalities in case of single match

Play
Player Win MatchTennis

Complete the E/A model for a simple match of Tennis

Exercise : Cardinalities in case of single match
Player MatchTennis
0,n 2,2
id Play
Name Id
0,n Win 1,1 DateTime
score

1. Can we play a double game Played on

2. Can a player win without participating
3. Can we have two tournaments happening at the
same time at the same location Tennis Location
4. Can we detect the location of the game from the id
id
of the player
Solution
1.Can we play a double game
•No, since the cardinality (2,2) means that only two players max can play a match
together
2.Can a player win without participating
•Based on the model, we can’t make sure that the one winning has already played
since no inclusion constraint exists between play and win
3.Can we have two tournaments happening at the same time at the same
location
• Based on the model, we can’t know if two tournaments are happening at
the same time at the same location since at the location many match can be
played(0,n) without highlighting a constraint on the level of the date
4. Can we detect the location of the game from id of the player
Yes from the association “played on” since 1,1 is stated as cardinality
116
Assignment: Lets Practice!
• A record company wishes to use a computer database to help with its operations regarding its
performers, recordings and song catalogue.
• Songs have a unique song number, a non-unique title and a composition date. A song can be
written by a number of composers; the composer’s full name is required. Songs are recorded
by recording artists (bands or solo performers). A song is recorded as a track of a CD. A CD has
many songs on it, called tracks. CDs have a unique record catalogue number, a title and must
have a producer (the full name of the producer is required). Each track must have the recording
date and the track number of the CD.
• A song can appear on many (or no) CDs, and be recorded by many different recording artists.
The same recording artist might re-record the same song on different CDs. A CD must have
only 1 recording artist appearing on it. CDs can be released a number of times, and each time
the release date and associated number of sales is required.
Database Design Phases

Relational Model

09/03/2022 120
Practice: Oracle Data Modeler

Database Design 121