Unit 4: Database Design & Development

LO1 :-Use an appropriate design tool

to design a relational database system
for a
substantial problem
• The role of database systems e.g. as backend systems, in ecommerce, for
data mining applications etc.
• Determining user and system requirements
• Design tools and techniques for a relational database system
• Logical design for relational databases e.g. tables, data elements, data types, indexes,
primary/foreign keys, entity relationship modelling, referential integrity, data normalisation to third
normal form
• Designs for data integrity, data validations, data security and data controls
• User interface design
• Output designs for user requirements
• Overview of object oriented databases and their design tools.
By the end of this session a student
will be able to learn:
1. File Oriented Systems and Database Systems

2. Components of a Database System

3. Database Management System and Functions

4. Data Dictionary/Directory (Repository) & Meta Data

5. Database Applications

6. Database Architecture
File Processing System
 File Processing System -
Issues
• Data Redundancy
• Data Inconsistency
• Data not Shareable
• Data Dependence
• Data Isolation
• Difficult to access and manipulate data
• Less data security
Database System
Database System Features
• Minimal data redundancy
• Data Consistency
• Data Shareable
• Data independence
• Data Integration.
• Easy to access and manipulate data
• High data security
Components of Database
System
1. Hardware
2. Software
◦a. DBMS
◦b. Application Software
3. People
◦a. Users
◦b. Practitioners
4. Data
DBMS & Functions
Data Dictionary

A subsystem that keeps track of the definitions of all data

items in the database, relationships that exists between
various data structures, Indexes that are used to access data
quickly, screen and report format definitions that may be
used by various application programs.
Meta Data
Data Describes data
Types of Databases
1. Personnel Computer Databases
2. Workgroup Databases
3. Department Databases
4. Enterprise Databases
5.Data warehouses and Data Marts
6.Distributed Databases
Data warehouses
Data warehouses
• Stores data that have been extracted from the various operational,
external and other databases
• A central source of the data that have been cleaned, transformed,
catalogued
• Can use for data mining, online analytical processing ,market
research and decision support
• A database for historical data
Data mining
• It is an information-analysis tool that involves the automated
discovery of hidden patterns and trends in historical business
activity.
• It helps to extract patterns and trends
• Turn will improve competitiveness, improve profits and transform
business processes.
Data Marts
• A sub set of a data warehouse.
• Store a subset of the data for a single aspect of a company
• Example: Finance, inventory, personnel
Distributed Databases
Distributed database
• Data spread across several databases connected through
telecommunications devices.
• The user does not need to have knowledge about the
physical location of the database.
• DBMS finds the location of data according to the user requirement.

Lesson Title
Database Architecture
Mapping
The processes of transforming requests and results between levels
are called mappings. There are two levels of mapping in the
architecture, one between the external and conceptual levels of the
system and one between the conceptual and internal levels.

1. External/conceptual mapping
2. Conceptual/internal mapping
Data Independence
The three-schema architecture can be used to explain the concept of
data independence, which can be defined as the capacity to change
the schema at one level of a database system without having to
change the schema at the next higher level.

1. Logical Data Independence

2. Physical Data Independence
LO1 :-Use an appropriate design tool
to design a relational database system
for a
substantial problem
• The role of database systems e.g. as backend systems, in ecommerce, for data mining applications etc.
• Determining user and system requirements
• Design tools and techniques for a relational database system
• Logical design for relational databases e.g. tables, data elements, data types, indexes,
primary/foreign keys, entity relationship modelling, referential integrity, data normalisation to third
normal form

• Designs for data integrity, data validations, data security and data controls
• User interface design

Lesson Title
 • Output designs for user requirements
• Overview of object oriented databases and their design tools.

Lesson Title
Introduction –Life cycle

Lesson Title
What is a requirement?
It may span a wide range of statements
◦ from a high-level abstract statement of a service or of a
system constraint
◦ to a detailed mathematical functional specification
Types of requirements
◦ User requirements
◦ System requirements
◦ Software specifications – provide more (design) detail
User requirements
These are written for the non technical customers
User requirements are defined using natural language, tables, and
diagrams
Problems with natural language
◦ Precision vs. understand ability
◦ Functional vs. non-functional requirements confusion
System requirements
•Written for the developers
•More detailed specifications of user requirements
•Serve as a basis for designing the system
•May be used as part of the system contract
Homework+# 1+ Solutions+3.+ Give+ an+example+to+distinguish+between+ user+ requiremen (Homework+#1+ Solutions+ 3.+Give+an+ example+ to+ distinguish+betw een+user+ requiremen.+ Give+an+example+ to+distinguish+between+ user+ requirements+ and+system+ requirements)

User requirements –Database

point of view
•All the student data should be stored for the future work
•The admin should be able to get the student the payment
summary at the end of the month

Lesson Title
System requirement –Database
point of view
•The student table should be created
•The payment table should be created
•Any payment should have the student Id as a foreign key
•The report generation tool should be connected

Lesson Title
ERD (Entity Relationship
Diagram) and EERD
(Enhanced Entity
Relationship Diagram

30
What is ERD?
• It is modeling tool used to depict graphically a database
design before it is actually implemented Determining
user and system requirements

Lesson Title
An entity
• An entity is a person, a place, an object, an event, or a
concept in the user environment about which the
organization wishes to maintain data
An Attribute
• Attribute A property or characteristic of an entity or
relationship type that is of interest to the
organization.
Composite attribute Vs. atomic
attribute
COMPOSITE ATTRIBUTE SIMPLE (OR ATOMIC) ATTRIBUTE

Composite attribute An attribute that has Simple (or atomic) attribute An attribute that
meaningful component parts cannot be broken down into smaller
(attributes). components that are meaningful to the
organization.

Lesson Title
Multivalued attribute Vs. Single
Valued
MULTIVALUED ATTRIBUTE SINGLE VALUED ATTRIBUTE

Multivalued attribute An attribute that may Single Valued attribute An attribute that
take on more than one value for a given may take only one value for a given entity
entity (or relationship) instance. (or relationship) instance.

Lesson Title
Stored versus derived attributes
DERIVED ATTRIBUTES STORED ATTRIBUTE

An attribute whose values can be An attribute whose values can be stored in the
calculated from related attribute values. database

Lesson Title
Identifier
•An attribute (or combination of attributes) whose
value distinguishes instances of an entity type.
•A super key of an entity set is a set of one or more
attributes whose values uniquely determine each entity.
•A candidate key of an entity set is a minimal super key
•Although several candidate keys may exist, one of the
candidate keys is selected to be the primary key.
An Example
Weak Entity Set
Some entity sets in real world naturally depend on some
other entity set
◦They can be uniquely identified only if combined
with another entity set
Example:
◦section1, section2, … become unique only if you put
them into a context, e.g. csce4350
Weak Entity Set Notations
•Double rectangles for weak entity set
•Double diamond for weak entity relationship
•Dashed underscore for discriminator
Relationship
A relationship may be thought as a set as well
◦ For binary relationship, it enumerates the pairs of
entities that relate to each other
◦ For example, entity set M = {Mike, Jack, Tom} entity set F
= {Mary, Kate}. The relationship set married between M
and F may be {<Mike,Mary>,<Tom, Kate>}
Relationship Example
Attribute on a Relationship Set
Relationship
The degree of a relationship = the number of entity sets that
participate in the relationship
◦ Mostly binary relationships
◦ Sometimes more
Mapping cardinality of a relationship
◦ 1 –1
◦ 1 – many
◦ Many-many
One-One and One-Many
Many-one and many-many
E-R Diagram: Chen Model
Entity
◦ represented by a rectangle with its name in
capital letters.

Relationships
◦ represented by an active or passive verb inside
the diamond that connects the related entities.

Connectivities
◦ i.e., types of relationship
◦ written next to each entity box.
E-R Diagram: Crow’s Foot Model

Entity
◦ represented by a rectangle with its name in
capital letters.

Relationships
◦ represented by an active or passive verb that
connects the related entities.

Connectivities
◦ indicated by symbols next to entities.
◦ 2 vertical lines for 1
◦ “crow’s foot” for M
Crow’s Foot Connections
Alternative Cardinality
Specification
Total Participation
• When we require all entities to participate in the
relationship (total participation), we use double lines
to specify

Every loan has to have

at least one customer
Associative Entity
An entity type that associates the instances of one or more
entity types and contains attributes that are peculiar to
the relationship between those entity instances.

Lesson Title
Attribute on the relation
It is probably obvious to you that entities have attributes,
but attributes may be associated with a many-to-many
(or one-to-one) relationship
Degree

•The number of entity types that participate in

a relationship.
•Unary -A relationship between instances of a single
entity type.
•Binary-Binary relationship is a relationship between
the instances of two entity types.

Lesson Title
Self Relationship (Unary
Relationship)
Sometimes entities in a entity set may relate to other
entities in the same set. Thus self relationship
Here employees mange some other employees
The labels “manger” and “worker” are called roles the self
relationship
Ternary Relationship
Can We Decompose a Ternary Relationship?
Some relationships that appear to be non-binary may be better
represented using binary relationships
◦ E.g. A ternary relationship parents, relating a child to his/her father
and mother, is best replaced by two binary relationships, father and
mother
◦Using two binary relationships allows partial information (e.g.
only mother being know)
◦ But there are some relationships that are naturally non-binary
◦E.g. works-on, why?
Converting Ternary to binary
Specialization
A lower-level entity set inherits all the attributes and
relationship participation of the higher-level entity set to
which it is linked.
A lower-level entity set may have additional attributes and
participate in additional relationships
Completeness constraint
◦total : an entity must belong to one of the lower-
level entity sets
Completeness constraint
partial: an entity need not belong to one of the lower-level
entity sets

Lesson Title
Disjoint rule
A rule that specifies that an instance of a super type may
not simultaneously be a member of two (or more)

Lesson Title
Disjoint rule
subtypes.

Lesson Title
Overlap rule

A rule that specifies that an instance of a super type may

simultaneously be a member of two (or more) subtypes.

Lesson Title
 ?
?
?

? ?
?

?
?
Logical design for
relational databases

Lesson Title
Components of relational model
Data structure
◦Tables (relations), rows, columns
Data manipulation
◦Powerful SQL operations for retrieving and
modifying data
Data integrity
◦Mechanisms for implementing business rules
that maintain integrity of manipulated data
66
Relation
• A relation is a named, two-dimensional table of data.
• A table consists of rows (records) and columns (attributes or fields).
• Requirements for a table to qualify as a relation:
◦ It must have a unique name.
◦ Every attribute value must be atomic (not multivalued, not composite).
◦ Every row must be unique (can’t have two rows with exactly the same values for all their
fields).
◦ Attributes (columns) in tables must have unique names.
◦ The order of the columns must be irrelevant.
◦ The order of the rows must be irrelevant.
Correspondence with E-R Model
• Relations (tables) correspond with entity types and
with many-to-many relationship types.
• Rows correspond with entity instances and with many-
to- many relationship instances.
• Columns correspond with attributes.
Key Fields
• Keys are special fields that serve two main purposes:
• Primary keys. Unique identifiers of the relation. Examples include employee
numbers, social security numbers, etc. This guarantees that all rows are unique.
• Foreign keys .Identifiers that enable a dependent relation (on the many side of
a relationship) to refer to its parent relation (on the one side of the
relationship).

69
Schema for four relations (Pine Valley Furniture Company)

Primary Key
Foreign Key
(implements 1:N relationship
between customer and order)

Combined, these are a composite

primary key (uniquely identifies the
order line)…individually they are
foreign keys (implement M:N
relationship between order and product)

70 70
Integrity Constraints
•Domain Constraints
• Allowable values for an attribute.

Entity Integrity
• No primary key attribute may be null. All primary
key fields MUST have data

71
Integrity Constraints
Referential Integrity–rule states that any foreign key value (on
the relation of the many side) MUST match a primary key
value in the relation of the one side. (Or the foreign key can
be null)
◦ For example: Delete Rules
◦ Restrict–don’t allow delete of “parent” side if related rows exist in “dependent” side
◦ Cascade–automatically delete “dependent” side rows that correspond with the “parent” side row to be deleted
◦ Set-to-Null–set the foreign key in the dependent side to null if deleting from the parent side  not allowed for
weak entities

72
 Referential integrity constraints (Pine Valley Furniture)

Referential integrity constraints are drawn via arrows from

dependent to parent table

73 73
Transforming EER Diagrams
into Relations
Mapping Regular Entities to Relations
◦ Simple attributes: E-R attributes
map directly onto the relation
◦ Composite attributes: Use only their
simple, component attributes
◦ Multivalued Attribute: Becomes a
separate relation with a foreign key taken
from the superior entity
74
 Mapping a regular entity

(a) CUSTOMER entity type with simple attributes

(b) CUSTOMER relation

75
 Mapping a composite attribute

(a) CUSTOMER entity type with composite attribute

(b) CUSTOMER relation with address detail

76 76
Mapping an entity with a multivalued attribute

(a)

Multivalued attribute becomes a separate relation with foreign key

(b)

One-to-many relationship between original entity and new relation

77
Transforming EER Diagrams
into Relations (cont.)
Mapping Weak Entities
Becomes a separate relation with a foreign
key taken from the superior entity
Primary key composed of:
Partial identifier of weak entity
Primary key of identifying relation (strong entity)

78
Example of mapping a weak entity

a) Weak entity DEPENDENT

79 79
 Example of mapping a weak entity (cont.)

b) Relations resulting from weak entity

NOTE: the domain constraint for the foreign key should NOT allow null value if
DEPENDENT is a weak entity
Foreign key

Composite primary key

80 80
Transforming EER Diagrams into
Relations (cont.)
Mapping Binary Relationships
One-to-Many–Primary key on the one side
becomes a foreign key on the many side
Many-to-Many–Create a new relation with the
primary keys of the two entities as its primary key
One-to-One–Primary key on mandatory side
becomes a foreign key on optional side
81
 Example of mapping a 1:M relationship
a) Relationship between customers and orders

Note the mandatory one

b) Mapping the relationship

Again, no null value in the

foreign key…this is because
of the mandatory minimum
cardinality
Foreign key

82 82
 Example of mapping an M:N relationship
a) Completes relationship (M:N)

The Completes relationship will need to become a separate relation

83 83
 Example of mapping an M:N relationship (cont.)
b) Three resulting relations

Composite primary key

Foreign key
new
Foreign key
intersection
relation

84 84
Example of mapping a binary 1:1 relationship
a) In charge relationship (1:1)

Often in 1:1 relationships, one direction is optional

85 85
Example of mapping a binary 1:1 relationship (cont.)
b) Resulting relations
Example of mapping a binary 1:1 relationship (cont.)
Example of mapping a binary 1:1 relationship (cont.)
Foreign key goes in the relation on the optional side,
matching the primary key on the mandatory side

86 86
Transforming EER Diagrams
into Relations (cont.)
Mapping Associative Entities
Identifier Not Assigned
Default primary key for the association relation is
composed of the primary keys of the two
entities (as in M:N relationship)
Identifier Assigned
It is natural and familiar to end-users
Default identifier may not be unique

87
 Example of mapping an associative entity
a) An associative entity
 Example of mapping an associative entity (cont.)
b) Three resulting relations

Composite primary key formed from the two foreign keys

89 89
Example of mapping an associative entity with
an identifier
a) SHIPMENT associative entity

90 90
Example of mapping an associative entity with
an identifier (cont.)
b) Three resulting relations

Primary key differs from foreign keys

91 91
Transforming EER Diagrams
into Relations (cont.)
Mapping Unary Relationships
One-to-Many–Recursive foreign key in the same
relation
Many-to-Many–Two relations:
One for the entity type
One for an associative relation in which the
primary key has two attributes, both taken from
the primary key of the entity

92
Mapping a unary 1:N relationship

93 93
Mapping a unary M:N relationship

94
Transforming EER Diagrams
into Relations (cont.)
Mapping Ternary (and n-ary)
Relationships
One relation for each entity and one for the
associative entity
Associative entity has foreign keys to each
entity in the relationship

95
 Mapping a ternary relationship

a) PATIENT TREATMENT Ternary relationship

with associative entity

96 96
 Mapping a ternary relationship (cont.)

b) Mapping the ternary relationship PATIENT TREATMENT

97 97
Transforming EER
Diagrams into Relations
(cont.)
Mapping Supertype/Subtype Relationships
One relation for supertype and for each subtype
Supertype attributes (including identifier and subtype discriminator) go into supertype
relation
Subtype attributes go into each subtype; primary key of supertype relation also
becomes primary key of subtype relation
1:1 relationship established between supertype and each subtype, with supertype as
primary table
98
Supertype/subtype relationships

99 99
Mapping supertype/subtype relationships to relations

100 100
Normalization

Lesson Title
Data Normalization
Primarily a tool to validate and improve a logical
design so that it satisfies certain constraints
that avoid unnecessary duplication of data
The process of decomposing relations with
anomalies to produce smaller, well-structured
relations

102
Well-Structured Relations
A relation that contains minimal data redundancy and
allows users to insert, delete, and update rows without
causing data inconsistencies
Goal is to avoid anomalies
Insertion Anomaly–adding new rows forces user to create
duplicate data
Deletion Anomaly–deleting rows may cause a loss of data that
would be needed for other future rows
Modification Anomaly–changing data in a row forces changes
to other rows because of duplication
Anomalies in this Table
Insertion–can’t enter a new
employee without having the
employee take a course
Deletion–if we remove employee
140, we lose information about the
existence of a Tax Acc course
Modification–giving a salary
increase to employee 100 forces us
to update multiple records
Steps in normalization

105 105
 Functional Dependencies and Keys

Functional Dependency: The value of one attribute (the

determinant) determines the value of another attribute

Ex:- StudentName depends on studentID

106
First Normal Form
No multivalued attributes
Every attribute value is atomic
Table with multivalued attributes, not in 1st normal form

108 108
Table with no multivalued attributes and unique rows, in 1st
normal form

109 109
Second Normal Form
1NF PLUS every non-key attribute is fully
functionally dependent on the ENTIRE
primary key
Every non-key attribute must be defined by the
entire key, not by only part of the key
No partial functional dependencies

110
 Functional dependency diagram for INVOICE

OrderID  OrderDate, CustomerID, CustomerName, CustomerAddress

CustomerID  CustomerName, CustomerAddress
ProductID  ProductDescription, ProductFinish, ProductStandardPrice
OrderID, ProductID  OrderQuantity

Therefore, NOT in 2nd Normal Form

Removing partial dependencies

Getting it into Second

Normal Form

Partial dependencies are removed, but there

are still transitive dependencies

112
Third Normal Form
2NF PLUS no transitive dependencies (functional
dependencies on non-primary-key attributes)
Note: This is called transitive, because the primary key is a determinant for
another attribute, which in turn is a determinant for a third
Solution: Non-key determinant with transitive dependencies go into a new table;
non-key determinant becomes primary key in the new table and stays as foreign
key in the old table

113
Removing partial dependencies

Getting it into
Third Normal
Form

Transitive dependencies are removed

DE normalization
View Integration–Combining entities from multiple ER
models into common relations