it-im01.pdf

Database Development Process IT IM01: Advanced Database Systems Christine Joyce M. Carlos, MBusAn Ruth Ann G. Santos, MSIT Learning Objective At the end of the lesson, the students should be able to: a. Differentiate the stages in the database life cycle b. Understand the steps on how to design a...

Database Development Process IT IM01: Advanced Database Systems Christine Joyce M. Carlos, MBusAn Ruth Ann G. Santos, MSIT Learning Objective At the end of the lesson, the students should be able to: a. Differentiate the stages in the database life cycle b. Understand the steps on how to design a good database. 2 Lecture Outline Components of Information Systems Database Life Cycle (DBLC) The Design of the Database 3 Review Define the following: Data A piece of knowledge that can be interpreted Database A shared, integrated computer structure that stores a collection of data, as well as, metadata Metadata Data about the data DBMS Software systems that serves as an intermediary between the database and user; used to store, retrieve, define and manage data 4 Components of Information System Hardware Hardware: physical components of an information system Software: applications that carry out instructions on Process Software what to do Network Communication: Ability to exchange data and share resources among two or more devices Information System Database: Collection and organization of raw-facts People: users of Information Systems People Network Commu- Process: series of steps undertaken to achieve a nications desired outcome 5 Database Database Life Cycle The database life cycle Requirement (DBLC) is described as Analysis Design Stage the stages included for implementing a database, which begins Monitoring, with the analysis of the Modification and Logical Design requirements and ends Maintenance with the checking and Post - modification. Design Stage 6 Implementation Physical Design Phase 1: Requirement Analysis Data Requirement Specification Involves evaluating the details needed in an organization so that the database can be designed to produce the required information Interview both data producers and users Business objectives will dictate the database design 7 Phase II: Logical Design Creating conceptual models and normalizing data tables Conceptual model is a description of the structure of database usually represented by a Entity – Relationship (ER) diagram that shows the tables, fields, and primary keys of the database, and how tables are related (linked) to one another 8 Phase II: Logical Design Normalizing data tables is the process of applying increasingly stringent rules to reduce data redundancy and other problems related to design. 9 Phase II: Logical Design Ensures that the notional and logical data structure requirements are met Notional: Reflect the reality of the phenomena, their characteristics, and their relations Logical: Follow technical standards, remain accessible for modification, and efficient for data access 10 Phase III: Physical Design This stage aims to optimize the productivity of the database Finding ways to accelerate RDBMS performance. Extracting of data from database and writing data into it which are the slowest operations in RDBMS can speed up by manipulating some elements of database design. 11 Phase III: Physical Design Physical Structure: An acceptable database is when physical manifestation matches realities. Some components of the physical structure that need to be observed: Assertion: The data to be entered must be acceptable or valid. If we add a date, for example, its value and structure must be correct. Data presentation: It gives the specific type and size of data such as textual, imaginary, logical, numerical, etc. Data management and storage method: The more advanced the management and storage methods are, the less time is needed to monitor the database behavior. 12 Design Stage: Physical Structure of DB Data table: The file in table form represented by the entities or data sources Field: The table columns which are represented by the attributes. Record: The table rows, also known as tuple. Elementary item: Values per table cell. 13 Phase IV: Implementation During the implementation stage of the DBLC, the tables developed in the ER diagram (and subsequently normalized) are converted into SQL statements. These SQL statements are then executed in the RDBMS to create a database. 14 Phase V: Monitoring, Modification, and Maintenance A successfully implemented database must be carefully monitored to ensure that it is functioning properly and that it is secure from unauthorized access. The RDBMS usually provides utilities to help monitor database functionality and security. Database modification involves adding and deleting records, importing data from other systems (as needed), and creating additional tables, user views, and other objects and tools. As an organization grows, its information system must grow to remain useful 15  6 Steps in Designing a Database Declaration Identification of identifiers Requirement of entities such Establishment of Testing Data input Handling and their primary and relationships attributes foreign keys. Define the Once the Establish Show the Once the tables objectives of entities are Relationships connection are created and the database identified, between keys are in place, testing is that will be table relations required to the basis of structures ensure that the the database can be design does design. specified not cause any inconsistencies 16 THANK YOU 17 Advanced Data Modeling Concepts I DATA MODELS IT IM01: Advanced Database Systems Christine Joyce M. Carlos, MBusAn Ruth Ann G. Santos, MSIT Learning Objective At the end of the lesson, the students should be able to: a. Identify appropriate data model for a specific system/application; b. Create an EER design; c. Apply the concept of supertype or subtype relations; d. Derive normalized forms of the database. 2 Lecture Outline Data Model Different Data Models Centralized vs. Decentralized Design OLTP vs OLAP Review: Entity Relationship Diagram Crow’s Foot Notation 3 Data Models Refers to the process of creating a specific data representation for a determined business problem “Blueprint” with all the instructions to build a database that will meet all end-user requirements 4 Data Model An implementation-ready data model should contain at least the following components: Description Set of Data of the data enforceable manipulation structure rules methodology 5  Types of Data Models Object Hierarchica Relational Oriented l Data Data Data Model Model Model 1960 1970 1985 1969 1983 2009 Network Internet NoSQL/ Data was born NewSQL Model 6 Hierarchical Data Model A tree-like data model that stores data in a hierarchic structure. Each record-type is represented by a node in the tree. Organize records not relationships 7 Network Data Model Advancement of a hierarchical data model. Data in a network data model may have one or many ' ancestors. Allows navigation Main Disadvantages: Complicated Relationships Need for Large Storage Capacity 8 Relational Data Model A model composed of datasets that may have different types but also contain fields that may be related to other tables. These fields serve as a link to another table to form a relation. Structural independence promoted by use of independent tables 9 Relational Data Model The relational database table resembles a file (similar but with a crucial difference) Records are organized into tables, with relationships established between tables (through keys) Implemented through a Relational Database Management System (RDBMS) The RDBMS software translates a user’s logical requests (queries) into commands that physically locate and retrieve the requested data. 10 Relational Data Model Started database revolution because of: Its conceptual simplicity Its powerful and flexible query language - Structured Query Language (SQL) Allows the user to specify what must be done without specifying how. The RDBMS uses SQL to translate user queries into instructions for retrieving the requested data. SQL makes it possible to retrieve data with far less effort than any other database or file environment. 11 Object Oriented Data Model Follows the concepts of the use of object-oriented technology in database creation Since object oriented, we can store pictures, audio, video, and other types of data, which was previously impossible to store with the relational approach 12 NoSQL Data Model A new generation of databases that address the specific challenges of the Big Data era and have the following general characteristics: They are not They support They provide They support They are based on the highly high very large geared relational distributed scalability, amounts of toward model and database high sparse data performance SQL architectures availability, rather than and fault transaction tolerance. consistency 13 Database Centralized Design The collection of data that is stored, located, and maintained in a single location Usually employed when dealing with small–scale databases with a small number of objects and procedures. Cons: Difficult for complex relationships High Data traffic Vulnerable to data theft 14 Database Decentralized Design Ideal for a bigger number of entities and complex relations The task is divided into several modules Data components are divided into submodules Each submodule will have its own conceptual model and verification process After the verification process, all modules are integrated into one conceptual model, supporting all required transactions 15 OLTP vs OLAP Online Transaction Processing (OLTP) Online Analytical Processing (OLAP) OLTP has the work to administer day-to- Used for data analysis for business day transactions in any organization. decisions The main goal of OLTP is data processing Provides an environment to get insights not data analysis. from the database retrieved from multiple database systems simultaneously. Commonly referred to as “database” Commonly referred to as Data Warehouses 16  Three Important Elements of Data Model A person, place, thing, or event about which data will be Entity collected or stored Represents a particular type of object in the real world A characteristic of an entity Attribute Equivalent of fields in file systems Describes an association among entities Relation Three Types of relationships: one-to-many, many-to-many, and one-to-one 17 All of these are an equal part of a data model. None of each element is greater than the other. Properties of Data Model Attributes Relationships External Structure Internal Structure Data Types Data Domain 18 ER Model Process I. Identify the business rules based on the description of operations II. Identify the main entities and relationships from the business rules III. Identify the attributes and primary keys that adequately describe the entities IV. Develop the initial ERD V. Review and revise the ERD, as needed 19 ERD Notations Element Chen’s Notation Crow’s Foot Notation Entity ENTITY NAME Attributes attribute Name Relationship relationship 20 ERD Notations Relationship Chen’s Notation Crow’s Foot Notation One to one One to Many Many to Many 21 Relationship Type For Chen’s notation, we indicate the cardinality or the maximum number of times that an instance of one entity can be associated with instances in the related entity For Crow’s foot notation, we indicate both the cardinality and ordinality or the minimum number of times an instance in one entity can be associated with an instance in the related entity 22 Crow’s Foot Notation: Relationship Type Ordinality Cardinality 23 Crow’s Foot Notation: Relationship Type Crow’s Foot Notation also allows you to identify which relationships are mandatory and which are optional Ordinality = 1 → Mandatory Ordinality = 0 → Optional 24 Examples: One seat is assigned to one student One instructor can teach multiple course but a course can only be taught by one instructor A student can take different courses A professor may or may not teach a class 25 Example: ERD Crow’s Foot notation 26 Example 27 Example: Tiny College (TC) is divided into several schools: business, arts and sciences, education, and applied sciences. Each school is administered by a dean who is a professor. Each professor can be the dean of only one school, and a professor is not required to be the dean of any school. Therefore, a 1:1 relationship exists between PROFESSOR and SCHOOL. Note that the cardinality can be expressed by writing (1,1) next to the entity PROFESSOR and (0,1) next to the entity SCHOOL. 28 Database Design Challenge No matter how good the design is, design compromise can still arise depending on business priorities. Example of priorities Processing speed: Not storing derived attributes to obtain minimal access time Information Requirements: Expanding number of attributes by incorporating data transformations to get desired attributes Cost 29 THANK YOU 30 Advanced Data Modeling Concepts II ENHANCED ENTITY RELATIONSHIP IT IM01: Advanced Database Systems Christine Joyce M. Carlos, MBusAn Ruth Ann G. Santos, MSIT Learning Objective At the end of the lesson, the students should be able to: a. Identify appropriate data model for a specific system/application; b. Create an EER design; c. Apply the concept of supertype or subtype relations; d. Derive normalized forms of the database. 2 Lecture Outline Enhanced Entity Relationship Model EER Features and Relationships EER Constraints 3 Enhanced Entity Relationship Model Also known as Extended ER Model Result of adding more features and relationships to the original ER model Supertypes Generalization Specialization and subtypes Aggregation Composition 4 EER vs ER Diagram Both diagrams make designing your database easier. Gives you the visual outlook of your database. ER Diagram It details the relationships and attributes of its entities, paving the way for smooth database development in the steps ahead. Best for taking a more detailed look at your EER information. When your database contains a larger amount Diagram of data, it’s best to turn to an enhanced version to more deeply understand your model. 5 Supertype and Subtypes Supertype - It is a general entity type that may be related to a SUPERTYPE single or more subgroup Subtype - It is a more specific subgrouping that shares attributes with the supertype. SUBTYPES 6 Supertype and Subtypes Supertype - It is a general entity type that may be related to SUPERTYPE a single or more subgroup Subtype - It is a more specific subgrouping that shares attributes SUBTYPES with the supertype. 7 Supertype and Subtypes Supertype - It is a general entity type that may be related SUPERTYPE to a single or more subgroup Subtype - It is a more specific subgrouping SUBTYPES that shares attributes with the supertype. 8 Generalization It is the process of determining entities with common attributes to form a more general entity type and is also known as a supertype entity. 9 Generalization Supertype Indicates that a subtype is a subset of a supertype Subtypes 10 Specialization Process of determining one or more subtype from the supertype to create a supertype or a subtype relationship. 11 Specialization Process of determining one or more subtype from the supertype to create a supertype or a subtype relationship. 12 Aggregation When two entities having relation is treated as a sole entity Sometimes called the “Has a” relationship. 13 Aggregation When two entities having relation is treated as a sole entity Sometimes called the “Has a” relationship. 14 Composition Also known as a special or restricted aggregation The existence of one entity is dependent on the existence of another 15  EER Constraints : Completeness Constraints Completeness Constraints is a type of constraint used to determine or specify if an occurrence of an entity supertype can also be included to least one subtype. Total Specialization Rule: Each Partial Specialization Rule: An instance of an entity in supertype occurrence of an entity in supertype is should be a part of a subtype in the permissible not to belong to any subtype relation. Denoted by a Denoted by a double line single line 16  EER Constraints: Disjointness Constraint Disjointness Constraint denotes Overlap Rule states that a single an “either or” scenario wherein the entity can be a part of two or more supertype can belong to one of the subtypes subtypes but cannot belong to others. Denoted by a small letter o Denoted by a in a circle small letter d o in a circle d 17 EER Constraints: Disjointness Constraint Disjointness Constraint denotes an Overlap Rule states that a single “either or” scenario wherein the entity can be a part of two or more supertype can belong to one of the subtypes subtypes but cannot belong to others. Teacher Non Teaching Teaching Employees Resear Employees Dean cher disjoint 18 overlapping Notations 19 EERD Example An organization depends on a number of different types of members for its successful operation. Three types of members are of greatest interest: employees, volunteers and donors. Only employees have a date hired attribute, and only volunteers have a skill attribute. Donors only have a relationship (named Donates) with an item that has number and name. A donor must have donated one or more items, and an item may have no donors or one donor. The organization is interested in the following attributes for all of these person: Name, Address, and phone number. A person may have multiple telephone numbers. There are persons other than employees, volunteers, and donors who are of interest to the organization. On the other hand, at a given time a person may belong to two or more of these groups. Assumption: Potential donors are not included in this database 20 EERD Example 21 THANK YOU 22 Advanced Data Modeling Concepts III NORMALIZATION IT IM01: Advanced Database Systems Christine Joyce M. Carlos, MBusAn Ruth Ann G. Santos, MSIT Learning Objective At the end of the lesson, the students should be able to: a. Identify appropriate data model for a specific system/application; b. Create an EER design; c. Apply the concept of supertype or subtype relations; d. Derive normalized forms of the database. 2 Lecture Outline Define Normalization and Data Redundancy Data Redundancy Anomalies Normalization Objectives, Advantages, and Goals Identify Dependencies Addressed in Normalization 3 Conceptual Model to Relational Model Conceptual Models like ER Diagrams is a great way of designing and representing the database design in more of a flow chart form. We can generate a relational database schema using the ER diagram, by keeping in mind the following: Entity gets converted into a Table, with all the attributes becoming fields(columns) in the table. Primary Keys should be properly set. 4 Normalization A process of structuring attributes on a relational database to eliminate data repetition/redundancy. Data redundancy occurs when the same piece of data exists in multiple places 5 Normalization: Anomalies Insertion Anomaly – You cannot add up data on new doctors until they have a patient of their own. 6 Normalization: Anomalies Deletion Anomaly – If Doctor Jekyll has only one patient (David), and his record got deleted accidentally, then the data about Doctor Jeckyll will also be deleted 7 Normalization: Anomalies Modification Anomaly – Supposed Doctor Hyde leaves the hospital and be replaced by Dr John. Then we have to update all the records of patients of Doctor Hyde to reflect Doctor John’s data 8 Normalization: Objectives To avoid anomalies To reduce in insertion, restructuring of modification, and tables. deletion. To make a neutral To make the query of the models more collection of informative. relations despite the fluidity of data. 9 Advantages of Normalization More data storage as Faster maintenance redundancy removal of data because of the fewer results to a smaller size indexes. database. Also, by narrowing of Smaller database size tables, we can easily which then has an outcome specify which tables are to of faster response time. be joined. 10 Goals of Normalization Process Each row/column Each relation (table) intersection contains represents a single only one value and not a subject. group of values All nonprime attributes in a No data item will be relation (table) are dependent unnecessarily stored in on the primary key. The data more than one table is uniquely identifiable by a primary key value Each relation (table) has no insertion, update, or deletion anomalies, which ensures the 11 integrity and consistency of the data Normalization Normalization works through a series of stages called normal forms 12 Functional Dependence The attribute B is functionally dependent on the attribute A if each value of A uniquely determines value of B. In the Student table, Roll_No → Student_Name (A determines B) 13 Functional Dependence The attribute B is functionally dependent on the attribute A if each value of A uniquely determines value of B. In Normalization, we will deal with Transitive Dependence and Partial 14 Dependence Partial Dependency When the determinant of a functional dependency is only an attribute or a part of the primary key. Partial dependencies tend to be straightforward and easy to identify. If (A, B) → (C, D), B → C, and (A, B) is the primary key, then the functional dependence B → C is a partial dependency because only part of the primary key (B) is needed to determine the value of C. 15 Partial Dependency In the table above, Employee ID and Task No are candidate primary keys. Note that Employee Name can be determined using Employee ID alone while Task Name can be determined using Task_no alone. 16 Transitive Dependency It exists when there is an indirect relationship between the two attributes/fields If A → B and B → C are valid functional dependencies, the dependency A → C is a transitive dependency because A determines the value of C via B 17  Transitive Dependency Book → Author If you know the book’s name, you can learn the author’s name Author → Author Nationality If you know the author’s name, you can easily identify his nationality Thus, if you know the Book name, you can also determine the author’s nationality (Book → Author Nationality) 18 Summary of Dependencies Functional Dependence A → B ( A determines B): For every value of A, there is a unique value of B Transitive Dependence Q → E (Q is a transitive dependency of E): If Q is functionally dependent on W, and W is functionally dependent on E, then Q is transitively dependent on E Note: Check on non-key fields/columns: If there are fields that can be used to determine other non-key fields, transitive dependence might be present Partial Dependence If (A,B) are primary keys and C is functionally dependent on A alone, then C is partially dependent on A Note: Only happens when there are multiple primary keys on a table (Composite Primary Key) 19 Restaurant Mgmt Application Imagine we're building a restaurant management application. That application needs to store data about the company's employees and it starts out by creating the following table of employees: What is the Primary Key? 20 Restaurant Mgmt Application Imagine we're building a restaurant management application. That application needs to store data about the company's employees and it starts out by creating the following table of employees: Functional Dependence: Are all of these columns dependent on and specific to the primary key? 21 Restaurant Mgmt Application Imagine we're building a restaurant management application. That application needs to store data about the company's employees and it starts out by creating the following table of employees: Transitive Dependence: Do any of the non-primary key fields depend on something other than the primary key? 22 Restaurant Mgmt Application Imagine we're building a restaurant management application. That application needs to store data about the company's employees and it starts out by creating the following table of employees: Partial Dependence: Do any of the non-primary key fields depend on only one of the primary keys? 23  Student Data Table Student Student Fees Date of Teacher Teacher Course Address Subject 1 Subject 2 Subject 3 ID Name Paid Birth Name Address Name 3 Main Street, James 2000- John 18-Jul- 4-Aug- Economics 1 Biology 1 44 March Way, North Boston Peterso Economics 12345 Smith 00 91 (Business) (Science) Glebe 56100 56125 n 16 Leeds Road, Business James 2000- Maria 14- 10-Sep- Biology 1 Programm 44 March Way, Computer South Boston Intro Peterso 23456 Griffin May-01 92 (Science) ing 2 (IT) Glebe 56100 Science 56128 (Business) n 21 Arrow 2000- Susan 3-Feb- 13-Jan- Biology 2 Sarah Street, South Medicine 54628 Johnson 01 91 (Science) Francis Boston 56128 14 Milk Lane, 2000- Matt 29- 25-Apr- Shane 105 Mist Road, South Boston Dentistry 95634 Long Apr-02 92 Cobson Faulkner 56410 56128 What is the Primary Key? 24  Student Data Table Student Student Fees Date of Teacher Teacher Course Address Subject 1 Subject 2 Subject 3 ID Name Paid Birth Name Address Name 3 Main Street, James 2000- John 18-Jul- 4-Aug- Economics 1 Biology 1 44 March Way, North Boston Peterso Economics 12345 Smith 00 91 (Business) (Science) Glebe 56100 56125 n 16 Leeds Road, Business James 2000- Maria 14- 10-Sep- Biology 1 Programm 44 March Way, Computer South Boston Intro Peterso 23456 Griffin May-01 92 (Science) ing 2 (IT) Glebe 56100 Science 56128 (Business) n 21 Arrow 2000- Susan 3-Feb- 13-Jan- Biology 2 Sarah Street, South Medicine 54628 Johnson 01 91 (Science) Francis Boston 56128 14 Milk Lane, 2000- Matt 29- 25-Apr- Shane 105 Mist Road, South Boston Dentistry 95634 Long Apr-02 92 Cobson Faulkner 56410 56128 Functional Dependence: Are all of these columns 25 dependent on and specific to the primary key?  Student Data Table Student Student Fees Date of Teacher Teacher Course Address Subject 1 Subject 2 Subject 3 ID Name Paid Birth Name Address Name 3 Main Street, James 2000- John 18-Jul- 4-Aug- Economics 1 Biology 1 44 March Way, North Boston Peterso Economics 12345 Smith 00 91 (Business) (Science) Glebe 56100 56125 n 16 Leeds Road, Business James 2000- Maria 14- 10-Sep- Biology 1 Programm 44 March Way, Computer South Boston Intro Peterso 23456 Griffin May-01 92 (Science) ing 2 (IT) Glebe 56100 Science 56128 (Business) n 21 Arrow 2000- Susan 3-Feb- 13-Jan- Biology 2 Sarah Street, South Medicine 54628 Johnson 01 91 (Science) Francis Boston 56128 14 Milk Lane, 2000- Matt 29- 25-Apr- Shane 105 Mist Road, South Boston Dentistry 95634 Long Apr-02 92 Cobson Faulkner 56410 56128 Transitive Dependence: Do any of the non-primary key fields 26 depend on something other than the primary key? Table Representations for Normalization Relational Notation Schema PROJECT (PROJ_NUM, EMP_NUM, PROJ_NAME, EMP_NAME, JOB_CLASS, CHG_HOUR, HOURS) Dependency Diagrams 27 Relational Notation Schema Relational notation is a process of transforming an E/R diagram into a more friendly and usable type of diagram that is easily readable. This can be done by 1. Taking the names of each table and its attributes and ordering them in a specific order. 2. Always start with the primary key(s), which are commonly notated with the underscore, Next all other attributes are added. 3. If an attribute happens to be a foreign key it needs to be underscored with a dotted line. TABLE_NAME(primary_key, foreign_key, nonkey_attribute1, nonkey_attribute 2, nonkey_attribute3) 28 Dependency Diagram Used to illustrate the dependencies determined in a table structure It also provides an overview of relationships that are existing on a table. It would reduce the risk of having an important dependency being overlooked. 29 Dependency Diagram Example The dependency diagram indicates that authors are paid royalties for each book they write for a publisher. The amount of the royalty can vary by author, by book, and by edition of the book. 30 Dependency Diagram Example The dependency diagram indicates that authors are paid royalties for each book they write for a publisher. The amount of the royalty can vary by author, by book, and by edition of the book. NonKey Attributes Primary Keys 31  Dependency Diagram Example The dependency diagram indicates that authors are paid royalties for each book they write for a publisher. The amount of the royalty can vary by author, by book, and by edition of the book. NonKey Attributes Primary Keys Transitive Dependency: Book Title → Publisher Partial Dependencies: 32 ISBN → Book Title, Publisher, Edition Author_Num → Last Name Normalization Process 33 Steps in Normalization: First Normal Form 1NF Repeating groups happens when a single attribute contains data of multiple entries with the same type. Removing repeating groups will reduce redundancies on a database 34 Steps in Normalization: 1NF Steps to First Normal Form Step 1. All Step 2. A Step 3. All repeating Primary key dependencies groups must must be in a table must be removed determined be identified No single key Identify/Create a Determine all attribute unique key or dependencies containing attribute on a that exist in the multiple entries of given table table data with the same type. 35 Steps in Normalization: 1NF Step 1 All repeating groups must be removed: No single attribute containing multiple entries of data with the same type. 36 Steps in Normalization: 1NF Step 2 A Primary key must be determined 37 Steps in Normalization: 1NF Step 3 All dependencies in a table must be identified 38 Steps in Normalization: Second Normal Form 2NF Conversion to 2NF occurs only when the 1NF has a composite primary key. If the 1NF has a single-attribute primary key, then the table is automatically in 2NF. Characteristics of Second Normal Form It MUST be in 1NF All attributes are dependent on the primary key. Partial dependencies does not exist 39 Steps in Normalization: Second Normal Form 2NF Steps to Second Normal Form: Step 1. Make New Step 2. Reassign Tables to Eliminate Corresponding Partial Dependencies. Dependent Attributes Specify key Most anomalies are components and place removed in the 2NF them on a separate column. Designate each key component as the primary key to new tables. 40 Steps in Normalization: 2NF Step 1 Make New Tables to Eliminate Partial Dependencies. 41 Steps in Normalization: 2NF Step 2 Reassign Corresponding Dependent Attributes 42 Steps in Normalization: Third Normal Form 3NF A table is in 3NF when it is in 2NF No nonkey attribute is functionally dependent on another nonkey attribute (Does not include transitive dependencies) 43 Steps in Normalization: Third Normal Form 3NF Steps to Third Normal Form: Step 1. Remove all Step 2. Reassign transitive dependencies dependency of attributes For every transitive The goal is to dependency, we can determine write PK for its dependencies in a determinant table A determinant is an attribute that may be used to determine the value of another row in a table. 44 Steps in Normalization: 3NF Step 1 Remove all transitive dependencies 45 Steps in Normalization: 3NF Step 2 Reassign dependency of attributes 46 Example: Normalization Process The DreamHome Customer Rental Details form holds details about property rented by a given customer. – To simplify things, we will assume that a renter rents a given property once and only one property at a time. 47 Example: Normalization Process Characteristics of First Normal Form Key attributes are determined Repeating groups are removed A primary key where all other attributes are dependent is specified 48 Example: Normalization Process 1NF Step 1 Step 1: All repeating groups must be removed Step 2: A Primary key must be determined Step 3: All dependencies in a table must be identified 49 Example: Normalization Process 1NF Step 2 Step 1: All repeating groups must be removed Step 2: A Primary key must be determined Step 3: All dependencies in a table must be identified Customer Rental Table CustNo CName PropNo PAddr RntSt RntFnsh Rent OwnerNo OName 50 Example: Normalization Process 1NF Step 3 Step 1: All repeating groups must be removed Step 2: A Primary key must be determined Step 3: All dependencies in a table must be identified Customer Rental Table CustNo CName PropNo PAddr RntSt RntFnsh Rent OwnerNo OName 51 Partial Dependencies Transitive Dependency Example: Normalization Process 2NF Characteristics of Second Normal Form It MUST be in 1NF All attributes are dependent on the primary key. Partial dependencies may exist in some tables: Partial dependencies exist when a dependency is based on a part of a primary key. Customer Rental Table CustNo CName PropNo PAddr RntSt RntFnsh Rent OwnerNo OName 52 Partial Dependencies Transitive Dependency  Example: Normalization Process 2NF Step 1 Step 1: Make New Tables to Eliminate Partial Dependencies. Customer Rental Table CustNo CName PropNo PAddr RntSt RntFnsh Rent OwnerNo OName Partial Dependencies Transitive Dependency Rentals Table Customer Table Property Owner Table CustNo PropNo RntSt RntFnsh CustNo CName PropNo PAddr Rent OwnerNo OName 53 Example: Normalization Process 2NF Step 1 Step 1: Make New Tables to Eliminate Partial Dependencies. 54 Example: Normalization Process 2NF Step 2 Step 2: Reassign Corresponding Dependent Attributes Rentals Table Customer Table CustNo PropNo RntSt RntFnsh CustNo CName Property Owner Table PropNo PAddr Rent OwnerNo OName 55 Transitive Dependency Example: Normalization Process 3NF A table is in 3NF when it is in 2NF No nonkey attribute is functionally dependent on another nonkey attribute Does not include transitive dependencies. Rentals Table Property Owner Table CustNo PropNo RntSt RntFnsh PropNo PAddr Rent OwnerNo OName Customer Table Transitive Dependency 56 CustNo CName Example: Normalization Process 3NF Step 1: Remove all transitive dependencies Step 2: Reassign dependency of attributes Rentals Table Property Table CustNo PropNo RntSt RntFnsh PropNo PAddr Rent OwnerNo OName Customer Table Owner Table CustNo CName OwnerNo OName 57 Example: Normalization Process 3NF 58 Normalization and Denormalization You should not assume that the highest level of normalization is always the most desirable. Higher More More Normal Relational Resources Form Join to Respond Operations to Queries Denormalization produces a lower normal form that may increase performance but has greater redundancy 59 THANK YOU 60