Software Engineering Lecture 11 Software Design Database Design

Software Engineering Lecture 11 Software Design: Database Design

Database Design Three Types of Database Design The Relational Model Normalization 2

Three Types of Database Design From Existing Data ◦ Analyze spreadsheets and other data tables ◦ Design using normalization principles New Systems Development ◦ Create data model from application requirements ◦ Transform data model into database design Database Redesign ◦ Migrate databases to newer databases ◦ Integrate two or more databases 3

Database Design from Existing Data 4

Data Import: One or Two Tables? This is an important decision, and based on a set of rules known as normalization 5

Database Design from New Systems Development 6

Database Design from Database Redesign 7

The Relational Model Introduced in 1970 Created by E. F. Codd ◦ He was an IBM engineer ◦ The model used mathematics known as “relational algebra” Now the standard model for commercial DBMS products 8

Important Relational Model Terms Entity Relation Functional Dependency Determinant Candidate Key Composite Key Primary Key Surrogate Key Foreign Key Referential integrity constraint Normal Form 9

Entity An entity is some identifiable thing that users want to track: ◦ Customers ◦ Computers ◦ Sales 10

A Relation 11

Tables That Are Not Relations: Multiple Entries per Cell 12

Functional Dependency A functional dependency occurs when the value of one (a set of) attribute(s) determines the value of a second (set of) attribute(s): Student. ID Student. Name Student. ID (Dorm. Name, Dorm. Room, Fee) The attribute on the left side of the functional dependency is called the determinant 13

Composite determinant: A determinant of a functional dependency that consists of more than one attribute (Student. Name, Class. Name) (Grade) 14

Keys A key is a combination of one or more columns that is used to identify rows in a relation A composite key is a key that consists of two or more columns 15

Candidate and Primary Keys A candidate key is a key that determines all of the other columns in a relation A primary key is a candidate key selected as the primary means of identifying rows in a relation: ◦ There is one and only one primary key per relation ◦ The primary key may be a composite key ◦ The ideal primary key is short, numeric and never changes 16

Surrogate Keys A surrogate key as an artificial column added to a relation to serve as a primary key: ◦ DBMS supplied ◦ Short, numeric and never changes – an ideal primary key! ◦ Has artificial values that are meaningless to users ◦ Normally hidden in forms and reports 17

Surrogate Keys NOTE: The primary key of the relation is underlined below: RENTAL_PROPERTY key: without surrogate RENTAL_PROPERTY (Street, City, State/Province, Zip/Postal. Code, Country, Rental_Rate) RENTAL_PROPERTY with surrogate key: RENTAL_PROPERTY (Property. ID, Street, City, State/Province, Zip/Postal. Code, Country, Rental_Rate) 18

Foreign Keys A foreign key is the primary key of one relation that is placed in another relation to form a link between the relations: ◦ A foreign key can be a single column or a composite key ◦ The term refers to the fact that key values are foreign to the relation in which they appear as foreign key values 19

Foreign Keys NOTE: The primary keys of the relations are underlined any foreign keys are in italics in the relations below: DEPARTMENT (Department. Name, Budget. Code, Manager. Name) EMPLOYEE (Employee. Number, Employee. Name, Department. Name) 20

The Referential Integrity Constaint A referential integrity constraint is a statement that limits the values of the foreign key to those already existing as primary key values in the corresponding relation 21

Normal Forms 1 NF – A table that qualifies as a relation is in 1 NF 2 NF – A relation is in 2 NF if all of its nonkey attributes are dependent on all of the primary key 3 NF – A relation is in 3 NF if it is in 2 NF and has no determinants except the primary key Boyce-Codd Normal Form (BCNF) – A relation is in BCNF if every determinant is a candidate key 22

Updateable vs Read-Only Database Updateable database or read-only database? If updateable database, we normally want tables in BCNF If read-only database, we may not use BCNF tables 23

Normalization: Advantages vs Disadvantages 24

Non-Normalized Table: EQUIPMENT_REPAIR 25

Normalized Tables: ITEM and REPAIR 26

Choosing Not to Use BCNF is used to control anomalies from functional dependencies There are times when BCNF is not desirable The classic example is ZIP codes: ◦ ZIP codes almost never change ◦ Any anomalies are likely to be caught by normal business practices ◦ Not having to use SQL to join data in two tables will speed up application processing 27

Read-only databases are non-operational databases using data extracted from operational databases They are used for querying, reporting and data mining applications They are never updated (in the operational database sense – they may have new data imported form time-to-time) 28

Denormalization For read-only databases, normalization is seldom an advantage ◦ Application processing speed is more important Denormalization is the joining of data in normalized tables prior to storing the data The data is then stored in non-normalized tables 29