WELCOME TO

DATABASE DESIGN (CMP2018)

Semester 1: 2023-24
Lecturer: Denise Allen
dnzealln@gmail.com or dallen@utech.edu.jm
COURSE BREAK DOWN
Class Session
¢Lecture – 1 Hour

¢Tutorial – 1 Hour

¢Lab - 3 Hours (1 Hour Supervised)

Course Breakdown:
¢Coursework – 60%

¢Final Exam/Assessment – 40%

Text Book:
¢Database System Concepts (6th) by Silberschatz
& Korth
COURSE WORK BREAKDOWN

¢ Group Assignment 1 – Due Week 5: - 10%

¢ Test 1 - Week 7 – (10)%

¢ Test 2 - Week 12 – (10)%

¢ Lab Test – Week 11 - 10%

¢ Tutorial Participation and Attendance – 5 & 5 %

¢ Continuous Lab Assessments (incl lab test 1) –

10%
COURSE PERSONNEL
Lecturer :
Ms. Denise Allen
Email: dallen@utech.edu.jm/dnzealln@gmail.com

Tutor(s):
Mr. Kenrayan Whittle, Ms. Christine Anuli & Ms. Alesia
Bowen-Mighty

Lab Technologists: Louis Dillon:

scitDBlabtech@gmail.com

David Barnwell:
dbarnwellutechit@gmail.com
Lecture 1 – Chapter 1
Silberschatz, Korth and Sudarshan:
Database System Concepts ( 5th Edition )
WHAT IS A DATABASE?
WHAT IS A DATABASE?

¢ The collection of data, usually referred to as the

database, contains information relevant to an
enterprise.

¢ Definition: a database is a collection of inter-

related records.

¢ Relational Database: collection of tables that

represent both the data and the relationships
that exist among the data.
WHAT IS A DATABASE?

¢ A database-management system (DBMS) is a

collection of interrelated data and a set of
programs to access those data.
WHAT IS A DATABASE?

Management Systems

Insert
Update
Delete

Database
WHAT IS A DATABASE?

Intro Video:

“Access 2010: Introduction to Databases”

¢http://www.youtube.com/watch?v=eXiCza050ug
WHAT IS A DATABASE?
¢ Database systems are designed to manage large
bodies of information.

¢ The primary goal of a DBMS is to provide a way

to store and retrieve database information that is
both convenient and efficient.

¢ The DMBS must also ensure the safety of the

information stored, despite system crashes or
attempts at unauthorized access
STRATEGIC DATABASE PLANNING
STRATEGIC DATABASE PLANNING

¢ a strategic corporate effort to determine the

information needs of the organization for an
extended period into the future
STRATEGIC DATABASE PLANNING

¢ Why is Database Planning so important?

STRATEGIC DATABASE PLANNING
¢ It expresses management’s current
understanding of the information resources

¢ It identifies and justifies resources requirements

—— helps to ensure that requirements are available

¢ It identifies opportunities for effective resource

management, including collaboration among
departments or divisions within the organization
STRATEGIC DATABASE PLANNING
Why is Database Planning important?

¢ It specifies action plans for achieving objectives

¢ It can provide powerful stimuli and sense of

direction to employees at all levels, focusing their
efforts, increasing their productivity and making
them feel genuine part of the organization.
DATABASE DEVELOPMENT LIFECYCLE
DATABASE DEVELOPMENT LIFE CYCLE

After the strategic database plan is completed and

receives final approval, then plans for designing
and implementing the database must be done. The
methodology for doing this is the database
development life cycle.
DATABASE DEVELOPMENT LIFE CYCLE
1. Preliminary Planning

2. Feasibility Study
— Technological
— Operational
— Economical

3. Requirements Definition

4. Conceptual Design

5. Implementation
RISKS AND COSTS OF DATABASES
¢ Organizational Conflicts

¢ Development Project failure

¢ System Failure

¢ Overhead Costs

¢ Need for Sophisticated Personnel

THREE LEVEL DATABASE ARCHITECTURE
THREE LEVEL
DATABASE ARCHITECTURE

¢ The external level

— Database structural level defining user views.
— E.g. partial version of the Student file containing only
names, ID and addresses of each student
¢ The Logical \ Conceptual level
— Database structural level defining logical schema of the
database. What data is stored and the relatonships that
exist between them.

¢ The Physical \ Internal level

— Database structural level defining physical view of
database. How data is actually stored
— E.g. indexes, the disk drives, physical addresses,
pointers
THREE LEVEL
DATABASE ARCHITECTURE
DATA MODELS
¢ Instance – collection of information stored in the
database at a particular moment in time.

¢ Schema – overall design of the database.

Physical, logical

¢ Data Model - a collection of conceptual tools for

describing data, data relationships, data
semantics, and consistency constraints. E.g.
Relational Model, ER Model, OO Model
 CONCEPTUAL MODELING USING
THE ENTITY RELATIONSHIP DIAGRAM (ERD)
Database System Concepts (6th Edition): Chapter 7
CONCEPTUAL MODELLING
¢ It is the blueprint of what will become your
database.

¢ This model captures the data and relationships

that exists between the different data elements

¢ The most important step is the scenario or

the requirements for which you want to
design the database
The Basic Components of an ERD
Database System Concepts (6th Edition): Chapter 7
AN ER MODEL HAS THREE BASIC COMPONENTS

¢ Entities – These are normally nouns in your

scenario
(Eg. STUDENT, CAR, PRODUCTS)

¢ Attributes – usually facts or properties about the

nouns you want to store (Eg. FIRSTNAME,
LASTNAME of a Student)

¢ Relationships – verbs in your scenario (Eg.

Student OWNS a Car)
ENTITIES

¢ These are objects in the real world that are

capable of unique existence (i.e. one can be
distinguished from the other).
E.g. person, car, plant, flight reservation

¢ Entities are objects about which information is to

be recorded in the database.

¢ They are represented in the ER diagram by a

rectangle labelled with the name of the entity.

Student
ENTITY SET

¢ Set of entities of the same type that shares the

same properties.

— The set of all students at the University can be

defined as the entity set STUDENT

Stud_ID Fname Lname Gender DOB

1203 John Wick Male 12/21/1970
3129 Arya Stark Female 10/12/1990
8723 Dom Tortuga Male 5/27/1994
2036 Sponge Bob Male 11/11/2000

Student
ATTRIBUTES
¢ An entity is represented by a set of attributes, that
is descriptive properties possessed by all members
of an entity set.
E.g. Student (Student_ID, FirstName, LastName)

¢ They are represented by labelled ovals attached to

the entity.

¢ One attribute is designated as the identifier, it is

used to distinguish one instance of an entity from
another.

Example: Student_ID 1001 from 1002

ATTRIBUTES: EXAMPLE
TYPES OF ATTRIBUTES

¢ Single Valued
¢ Composite

¢ Multi-valued

¢ Derived

¢ Unique
MOVING ON TO ATTRIBUTES:
SINGLE VALUE
¢ Attribute is atomic – cannot be broken down into
sub parts
¢ Holds only one value

Denoted By:

Example
ATTRIBUTES: COMPOSITE
¢ These are attributes that can be divided into subparts.
¢ a good choice if a user will wish to refer to an entire
attribute on some occasions, and to only a component of
the attribute on other occasions
Example: Name into Firstname, Lastname, Middle Initial
Address into Street, Parish, Country, Postal Code

¢ Represented as:
ATTRIBUTES: MULTI-VALUED

¢ There may be instances where an attribute has a

set of values for a specific entity.

¢ This may have several values.

¢ Example: Multiple Emails, multiple phone

numbers, more than one dependents

¢ Denoted by
ATTRIBUTES: MULTI-VALUED

¢ The scenario must indicate that there should be

a multi-valued attribute.

¢ Example: The university of technology would like

to store information on the students enrolled in
its courses. They would like to capture all
email addresses a student may have.
ATTRIBUTES: DERIVED

¢ Attributes that can be calculated by using other

existing attributes.

¢ The value for this type of attribute can be derived

from the values of other related attributes or
entities.

¢ Example: Age derived from DOB

¢ Denoted By:
ATTRIBUTES: UNIQUE IDENTIFIER

¢ The attribute that is used to identify one entity

from another.
¢ Non-repeating

Denoted as :
ATTRIBUTES: FULL EXAMPLE
RELATIONSHIPS

¢ A relationship represents the interaction between

entities.

¢ It is a meaningful association among entity

types. It expresses the idea that an instance of
one entity may participate in a relationship with
an instance of another entity type.

.
RELATIONSHIPS: EXAMPLE

Here we are showing the relationship between Student and course.

The focus is on Registers
RELATIONSHIPS

¢ On Our Diagram, relationships are represented

by a diamond.

¢ They are usually the verbs in our scenarios.

ERD BASICS

Starting Point:

¢ ERD is built from a scenario. The scenario dictates

what we know about an organization (or our client)
and how it operates.

¢Itsimply means forget what you thought you knew

about how another company operates, it doesn’t
matter.

ALWAYS STICK TO THE SCENARIO.

EVEN IF YOU THINK IT MAKES NO SENSE
ERD BASICS

ERD’s have three (3) basic components:

1.Entities which later on become relations (tables) if

you choose the relational model as your
implementation model.

2.Attributes – which later on become fields if you

choose the relational model as your implementation
model.

3. Relationships – Which tells you how entities are

related or “linked”
 THE END

NEXT WEEK: READ CHAPTER 7

CONCEPTUAL MODELLING