Database Design: Entity-Relationship Model and Table Relations - Prof. LOng, Study notes of Computer Science

Download Database Design: Entity-Relationship Model and Table Relations - Prof. LOng and more Study notes Computer Science in PDF only on Docsity!

Table Relations

Database Design and Rules

1. Database Design

2. Table Relations

3. Cascade Operations

4. E/R Diagrams

Table of Content

2

• (^) Requirement analysis: study the data required for processing, the natural data relationships, and the software platform for the database implementation
• (^) Logical design: create a conceptual data model diagram that shows all the data and their relationships.
• (^) Physical design: select access methods, partitioning and clustering of data
• (^) Implementation: create the database using the data definition language (DDL) and manipulate data with data manipulation language (DML)

Database life cycle

4 Logical design Physical design Implement Requirement analysis

LOGICAL

DESIGN

• (^) It is quite helpful to the database designer who must communicate

with end users about their data requirements

• (^) It describes, in diagram form, the entities, attributes, and

relationships that occur in the system

• (^) There are many approach methods:
  • (^) Object-based data models
  • (^) Record-Based Data Models
  • (^) Relational Data Model
  • (^) Network Data Model
  • (^) Hierarchical Data Model

Data models

• (^) ER model is a kind of object-based data model
• (^) Provide an intuitive image about different types of data

classification objects (entities, attributes, relationship, …)

• (^) The model in diagram is call ERD (Entity-Relationship

Diagram)

• (^) Typical online tool for this diagram design: www.draw.io

Entity-Relationship (ER) model

• (^) Attributes are clarifications for the entities in the text of the specification,

for example:

• (^) Students (Entity) have the following characteristics (attributes):
  • (^) Name, faculty number, photo, date of enlistment and a list of courses they visit

DB Design: Identify attributes

10 We need to develop a system that stores information about students, which are trained in various courses. The courses are held in different towns. When registering a new student the following information is entered: name, faculty number, photo and date.

• (^) Relationships are dependencies between the entities:
  • (^) "Students are trained in courses"  many-to-many relationship
  • (^) "Courses are held in towns"  many-to-one (or many-to-many) relationship

DB Design: Identify Entity Relationships

11 We need to develop a system that stores information about students, which are trained in various courses. The courses are held in different towns. When registering a new student the following information is entered: name, faculty number, photo and date.

ERD example

TABLE RELATIONS

Relational Model in Action

• (^) Each entity in an ERD is mapped to a single relation table
• (^) An attribute of an Entity in ERD is mapped to a column in

a associated relation table.

• (^) A row represents all pairings of attribute values of a

associated with entity occurrences in Entity.

Table transformation

• (^) Relationships between tables are based on

interconnections: primary key  foreign key

Table Relations

17

Countries

Id Name CountryId 1 Sofia 1 2 Varna 1 3 Munich 2 4 Berlin 2 5 Moscow 3 Id Name 1 Bulgaria 2 Germany 3 Russia

Towns

Primary key Foreign key^ Primary key Relationship

• (^) One-to-many – e.g. country / towns
  • (^) One country has many towns
• (^) Many-to-many – e.g. student / course
  • (^) One student has many courses
  • (^) One course has many students
• (^) One-to-one – e.g. example driver / car
  • (^) One driver has only one car
  • (^) Rarely used

Table Relations: Multiplicity

19

One-to-Many/Many-to-One

20 MountainID Name 5 Rila

Mountains

PeakID Name MountainID 61 Musala 5 66 Malyovitsa 5

Peaks

Primary key Primary key (^) Foreign key

Relation