CS 222 Lecture 4 DATABASE MANAGEMENT SYSTEM Somchai

CS 222 Lecture 4 DATABASE MANAGEMENT SYSTEM Somchai Thangsathityangkul

Example Manufacturer Database A manufacturing company produces products. The following product information is stored: product name, product ID and quantity on hand. These products are made up of many components. Each component can be supplied by one or more suppliers. The following component information is kept: component ID, name, description, suppliers who supply them, and products in which they are used. Create an ERD to show you would track this information. 2

Example Manufacturer Database Assumptions : • A supplier can exist without providing components. • A component does not have to be associated with a supplier. • A component does not have to be associated with a product. Not all components are used in products. • A product cannot exist without components. 3

Transforming the ER diagram into relation Schema diagram The steps: • Map regular entities • Map weak entities • Map binary relationships • Map unary relationships • Map ternary relationships • Mapping Supertype/subtype relationships

Mapping a composite attribute

Looks like this using relational schema Relational schema diagram : Relational schema : CUSTOMER( Custome_ID, Customer_Name, Street, City, State, Zip )

The 1: M Relationship Between PAINTER and PAINTING

Mapping a 1: M relationship

Looks like this using relational schema diagram

Mapping a binary 1: 1 relationship Some nurse is not in charge for care center (0, 1) All care centers must have a nurse to be in charge (1, 1) So, put FK on (0, 1) care center

Looks like this using relational schema diagram

Mapping an M: M relationship

Mapping an M: M relationship

Looks like this using relational schema diagram

Mapping weak entities

Looks like this using relational schema diagram

Mapping Supertype/subtype relationships

Looks like this using relational schema diagram

Try this

Functional Dependencies • Important concept associated with normalization. • Functional dependency describes relationship between attributes. • For example, if A and B are attributes of relation R, B is functionally dependent on A (denoted A B), if each value of A in R is associated with exactly one value of B in R.

Characteristics of Functional Dependencies • Property of the meaning or semantics of the attributes in a relation. • Diagrammatic representation. • The determinant of a functional dependency refers to the attribute or group of attributes on the left-hand side of the arrow.

An Example Functional Dependency

A Dependency Diagram

A Dependency Diagram

Try this : identify function dependencies

Database Tables and Normalization • Process for evaluating and correcting table structures to minimize data redundancies • Reduces data anomalies • Series of stages called normal forms: • First normal form (1 NF) • Second normal form (2 NF) • Third normal form (3 NF)

Database Tables and Normalization (cont’d. ) • Normalization (continued) • 2 NF is better than 1 NF; 3 NF is better than 2 NF • For most business database design purposes, 3 NF is as high as needed in normalization • Highest level of normalization is not always most desirable • Denormalization produces a lower normal form • Increased performance but greater data redundancy

Conversion to First Normal Form • A table is said to be in First Normal Form (1 NF) if and only if every entry of the table has at most a single value. • Objective: to remove a table’s repeating groups and ensure that all entries of the resulting table have at most a single value.

Conversion to First Normal Form (cont’d. ) • Step 1: Eliminate the Repeating Groups • Eliminate nulls: each repeating group attribute contains an appropriate data value • Step 2: Identify the Primary Key • Must uniquely identify attribute value • New key must be composed

Repeating Group example • Repeating group • Group of multiple entries of same type can exist for any single key attribute occurrence

First Normal Form (1 NF) Step 2 : Identify primary key Use line# to separate customer order item Step 1 : eliminate repeating group

Multi-value example

First Normal Form (1 NF) Step 2 : Identify primary key Step 1 : eliminate multi-value

(1 Nf) Dependency diagram

Second Normal Form (2 NF) • Table is in second normal form (2 NF) if: • It is in 1 NF and • It includes no partial dependencies: • No attribute is dependent on only a portion of the primary key

Second Normal Form (2 NF)

Third Normal Form (3 Nf) • A table is in third normal form (3 NF) when both of the following are true: • It is in 2 NF • It contains no transitive dependencies • Step 1: Make New Tables to Eliminate Transitive Dependencies • Step 2: Reassign Corresponding Dependent Attributes

Third Normal Form (3 Nf)

Final relational schema

The Data Dictionary • Data dictionary • Used to provide detailed accounting of all tables found within the user/designer-created database • Contains (at least) all the attribute names and characteristics for each table in the system • Contains metadata—data about data • Sometimes described as “the database designer’s database” because it records the design decisions about tables and their structures

Sample data dictionary

Try this • Doing normalization to 3 NF and draw a final schema diagram