Database Application Development PDF

Chapter Four Database Application Development Logical Database Design (Relational Data Model) 1 Database Management Systems  Basics...

Chapter Four Database Application Development Logical Database Design (Relational Data Model) 1 Database Management Systems  Basics of Relational Data Model Relational Model (RM)  Represents the database as a collection of related relations.  Relation is a named, two-dimensional table of data.  Each relation consists of a set of named columns and an arbitrary number of unnamed rows  Every row in the table represents a collection of related data values. These rows in the table denote a real-world entity occurrence or relationship.  The table name and column names are helpful to interpret the meaning of values in each row.  The physical storage of the data is independent of the way the data are logically organized. 2 Database Management Systems  Relational Model Notions  Tables – In the Relational model ,the relations are saved in the table format. A table has two properties rows and columns. Rows represent records and columns represent attributes.  Attribute/Fields: are the properties which define a relation. A column in a table represents the set of values for a specific attribute. Example :customer_id, customer_name,etc. Attribute domain – A domain is the original sets of atomic values used as a pre defined values for an attribute. By atomic value, we mean that each value in the domain is indivisible as far as the relational model is concerned.  Tuple – It is nothing but a single row of a table, which contains a single record.  Super key: An attribute, or set of attributes, that uniquely identifies a tuple within a relation. 3 Database Management Systems   Candidate Key :Super key (K) such that no proper subset is a super key within the relation. An attribute or group of attributes must ensure two properties to be a candidate key In each tuple of R, values of K uniquely identify that tuple (uniqueness). No proper subset of K has the uniqueness property (irreducibility).  Composite Candidate key- CK. that has more than one attribute  Primary Key: Candidate key selected to identify tuples/rows uniquely within relation/table.  Alternate Keys(Secondary key): Candidate keys that are not selected to be primary key.  Foreign Key: An attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation. It is a primary key of a relation that is used in another relation/table to identify instances of a relationship. 4 Database Management Systems   Foreign Key is a column or group of columns in a table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them. A foreign key always matches the primary key in the another relation/table A foreign key may or may not be unique  Relation Schema: A relation schema represents the name of the relation with its attributes. Example : customer(customer_id, customer_name, customer_gender)  Relation instance – Relation instance is a finite set of tuples in the RDBMS system. Relation instances never have duplicate tuples.  Well-Structured Relation –  a relation with minimal number of attributes with a close logical relationship and necessary to support the data requirements of the enterprise;  relation that contains a minimum amount of redundancy and allows users to insert, modify and delete the rows without errors or inconsistencies 5 Database Management Systems  Properties of a Table/Relation  A table/Relation has a name that is distinct from all other tables/relations in the database.  There are no duplicate rows; each row is distinct.  A table/ relation does not contain repeating groups or multivalued attributes.  Entries of columns are from the same domain based on their data type including: number (numeric, integer, float, smallint,…) character (string) date logical (true or false)  Operations combining different data types are disallowed.  Each attribute has a distinct name.  The sequence of columns is insignificant.  The sequence of rows is insignificant. 6 Database Management Systems  Transforming E-R Diagrams into Relational Models After designing the ER diagram of a system, the database designer need to convert it to Relational models which can be implemented by any RDBMS To transform the conceptual data model into a set of normalized relations , the following steps will be followed 1. Represent Entities` 2. Represent Relationships 3. Normalize the relations 4. Merge the relations In general ,the purpose of ER-to-Relations mapping is to create relations for the logical data model to represent the entities, relationships, and attributes that have been identified. And the conversion process has three components : – Specify schema of relation Specify primary key of the relation Specify any foreign key references to other relations 7 Database Management Systems  Represent Entities Strong Entity set into relational schema 1. Create a relational schema/Table with same Entity name (i.e strong entities are mapped as relation ) 2. Map Single-valued attributes of the Entity set as attributes for the Relation/Table. 3. Set Key attribute of the Entity to be Primary key for the Relation/Table. Note : Every entity occurrence is represented by a tuple in corresponding relation/Table Example Customer(customer_id, customer_name, customer_gender ) 8 Database Management Systems  Entity set with Composite attribute into relational schema 1. Create a relational schema/Table with same Entity name 2. Map Single-valued attributes of the Entity set as attributes for the Relation/Table. 3. Map child attributes of a composite attribute as simple attributes. the parent attribute will be ignored 4. Set Key attribute of the Entity to be Primary key for the Relation/Table. Note :To convert entity with a composite attribute into relational schema/Table, all parts of composite attribute are mapped as simple attributes instead of the composite attribute. Example: if customer_name is composite with parts customer_fname, customer_fathername and customer_gfatherName Instead of Customer(customer_id, customer_name, customer_gender ) the resulting relation will be Customer(customer_id, customer_fname, customer_fathername and customer_gfatherName, customer_gender ) 9 Database Management Systems  Entity set with multi valued attribute into relational schema to convert entity with multivalued attribute into relational schema 1. Create a relational schema/Table with its single value attributes as discussed above 2. Create a separate relation/Table for multivalued attribute in which Key attribute and multivalued attribute of entity set becomes primary key of relation. Example :for customer entity with single valued attributes customer_id, customer_name, customer_gender and multi valued attribute customer_phone the conversion results the following two relations Customer(customer_id, customer_name, customer_gender ) CustomerPhone(customer_id, customer_phone) 10 Database Management Systems  Weak Entity Sets into relational schema 1. Create a relational schema/Table with the weak Entity name 2. Map Single-valued attributes of the Entity set as attributes for the Relation/Table. 3. Add the primary key of identifying entity set. 4. Declare all foreign key constraints. 11 Database Management Systems  Represent Relationships Binary relations can be one of the following with total or partial participations 12 Database Management Systems  Binary Relationship with One to One (1:1) cardinality with total participation of an entity 1. Convert each entity and relationship to tables/Relations. 2. Check entity primary keys has only one entrance in the relationship table.it is possible to merge the three tables into one 3. Compare the primary keys of the entities and take the one which can not be null as primary key and the other make is unique. Example 13 Database Management Systems  A person has 0 or 1 passport number and Passport is always owned by 1 person. So it is 1:1 cardinality with full participation constraint from Passport. Person(Per_id,…….) Passport (Pass_No,……..) Has (Per_id, Pass_No) Merging results PersonPassport(Per-Id, Other Person Attribute,Pass-No,Other PassportAttribute) Each Per-Id will be unique and not null. So it will be the key. Pass-No can’t be key because for some person, it can be NULL. Note : If participation is Mandatory on both sides  Combine entities involved into one relation and choose one of the primary keys of original entities to be primary key of the new relation, while the other (if one exists) is used as an alternate key. 14 Database Management Systems  Binary Relationship with One to One (1:1 )cardinality and partial participation of both entities 1. Convert each entity and relationship to tables/Relations. 2. Check entity primary keys entrance in the relationship table. Since both entities participate partially it is not possible to merge the three tables into one. 3. Convert the three tables/relations into two tables by making one of the tables to contains the primary key copy of the other as Foreign key Example 15 Database Management Systems  some males and some females do not marry, hence the three tables cannot be merged but can be converted into two relations or tables as shown below Male (M-Id,Other Male Attribute, F-Id) Female(F-Id, Other Female Attribute) M-Id who are married will have F-Id associated. For others, it will be NULL Or Female(F-Id, Other Female Attribute, M-Id) Male (M-Id,Other Male Attribute) F-Id who are married will have M-Id associated. For others, it will be NULL 16 Database Management Systems  Binary Relationship with Many to One or One to Many ( M: 1 / 1:M)cardinality For each 1:* binary relationship, the entity on the ‘one side’ of the relationship is designated as the parent entity and the entity on the ‘many side’ is designated as the child entity. 1. Convert each entity and relationship to relation/ tables. 2. Merge the many side table and the relationship table into one 3. Copy of the primary key attribute of one’s side( parent) entity set is mapped as foreign key in Many side (child) relation All attributes of relationship set are mapped as attributes for Many’s side relation course. 17 Database Management Systems  Student (S-id,other student attributes) ElectiveCourse (E-id, other ElectiveCourse attributes) Enrolls(S-id,E-id) Merging Student and Enrolls results StudentEnrolls(S-Id,Other Student Attribute,E-id) ElectiveCourse (E-id, other student attributes) 18 Database Management Systems  Customer places an order Customer(Customer_Id, other customer attributes) Order(Order_Number, Order_Date, Promised_Date) Places (Customer_Id, Order_Number) Merging order the many side table with the relationship table places results Customer(Customer_Id, other customer attributes) Order(Order_Number, Order_Date, Promised_Date, Customer_Id) 19 Database Management Systems  Binary Relationship with Many to Many (M:N)cardinality 1. Convert each entity to tables/Relations and map their attributes 2. Convert Relationship/associative entity set as separate relation 3. a copy of the primary key attribute(s) of participating entity sets are mapped into the new relation, to act as foreign keys. These foreign keys will also form the primary key of the new relation. Attribute of the relationship/ associative entity set becomes simple attributes for this relation Example 20 Database Management Systems  Student (Rollno,StudentName,Class) Course(CourseCode,CourseName,Duration) Enroll(Rollno,Corsecode,dateofEnroll) 21 Database Management Systems  Unary Relationship with One to One(1:1) or One to Many (1:N) cardinality 1. Convert the entity into relation/ table. 2. Add a foreign key(renamed primary key) in a relation that references the primary key values of that same relation Example Course(CourseCode,Other Course attributes ,Prerequisite_Id) Where Prerequisite_Id is the Coursecode of the prerequisite course 22 Database Management Systems   Unary Relationship with Many to Many (M:N) cardinality 1. Convert the entity into relation/ table. 2. Convert the relation as a separate Relation/Table with Primary key which is a composite of two attributes (two different names given for the primary Key)that both take their values from the same primary key  Example Item(Item_Number,Name,other Item attributes) Contains(Containing_Item_Num, Contained_Item_Num, Quantity) 23 Database Management Systems  Guidelines for representation of superclass / subclass relationship  Superclass/subclass relationship types  Identify superclass entity as parent entity and subclass entity as the child entity. There are various options on how to represent such a relationship as one or more relations.  The selection of the most appropriate option is dependent on a number of factors such as the disjointness and participation constraints on the superclass/subclass relationship, whether the subclasses are involved in distinct relationships, and the number of participants in the superclass/subclass relationship.  The Primary key of a subclass is taken from the primary key of its Super type(Super class) 24 Database Management Systems 25 Database Management Systems  Data Normalization  Database Normalization is a technique of organizing the data in the database.  This includes creating tables and establishing relationships between those tables.  Normalization is used to minimize data redundancy from a relation or set of relations.  It is also used to eliminate the undesirable characteristics like Insertion, Update and Deletion Anomalies.  Normalization is used for mainly two purposes, Eliminating redundant(useless) data. Ensuring data dependencies make sense (that is data is logically stored).  If a table is not properly normalized and have data redundancy then it will not only eat up extra memory space but will also make it difficult to handle and update the database, without facing data loss. Insertion, Update and Deletion anomalies are very frequent if database is not normalized. 26 Database Management Systems   Normalization divide the larger table into the smaller tables and links them using relationship according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.  Two important properties of decomposition.  Lossless-join property enables us to find any instance of the original relation from corresponding instances in the smaller relations.  Dependency preservation property enables us to enforce a constraint on the original relation by enforcing some constraint on each of the smaller relations. 27 Database Management Systems   Normalization rules are called “Normal Forms” ,and divided into the following sequential normal forms : First Normal Form Second Normal Form Third Normal Form BCNF Fourth Normal Form  "If the first rule is observed, the database is said to be in "first normal form”. If the first three rules are observed, the database is considered to be in "third normal form."  Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications. 28 Database Management Systems  Process of normalization (1NF, 2NF, 3NF) First Normal Form (1NF) For a table to be in the First Normal Form, it should follow the following rules: It should only have single(atomic) valued attributes/columns. Each column of a table should be single valued which means they should not contain multiple values. Values stored in a column should be of the same domain Attribute Domain should not change. In each column the values stored must be of the same kind or type. All the columns in a table should have unique names. This rule expects that each column in a table should have a unique name. This is to avoid confusion at the time of retrieving data or performing any other operation on the stored data. And the order in which data is stored, does not matter. 29 Database Management Systems  Second Normal Form (2NF) For a table to be in the Second Normal Form,  It should be in the First Normal form.  And, it should not have Partial Dependency Full Vs Partial Functional Dependency :  An attribute B is fully functional dependent on another attribute A if it is functionally dependent on that attribute, and not on any part (subset) of it. AB reads “Attribute B is functionally dependent on A” – AB means if two rows have same value of A they necessarily have same value of B In full functional dependency , the non-prime attribute is functionally dependent on the candidate key.  An attribute B is partially functional dependent on other attribute A if it is functionally dependent any part (subset) of that attribute In partial functional dependency , non-prime attribute is functionally dependent on part of a candidate key. 30 Database Management Systems   Partial Dependency exists, when for a composite primary key, any attribute in the table depends only on a part of the primary key and not on the complete primary key.  To remove Partial dependency, we can divide the table, remove the attribute which is causing partial dependency, and move it to some other table where it fits in well.  Applying Second Normal Form eliminates repeating groups in relation.  The goal of the second normal form is to eliminate redundant data. Note :An attribute that is not part of any candidate key is known as non-prime attribute. 31 Database Management Systems  Example STUDENT Stude DateofEnroll ntId StuName StuFname StuGFName Gender Age Nationality CourseId CourseTitle CourseCode CrdtHrs ment Grade Fundamentals of 1 Abeba WORKU NEGUSSE F 18 Ethiopian 1 Programming CoSc2021 5 21/2/2020 B Advanced Computer 1 Abeba WORKU NEGUSSE F 18 Ethiopian 2 Programming CoSc2031 5 20/4/2020 A Fundamentals of Database 1 Abeba WORKU NEGUSSE F 18 Ethiopian 3 Systems CoSc2041 5 20/4/2020 B 1 Abeba WORKU NEGUSSE F 18 Ethiopian 4 Advanced Database Systems CoSc2042 4 19/7/2020 B Fundamentals of 3 Biniyam TADELE ZEWEDE M 23 Ethiopian 1 Programming CoSc2021 5 21/2/2020 A Advanced Computer 3 Biniyam TADELE ZEWEDE M 23 Ethiopian 2 Programming CoSc2031 5 20/4/2020 A Fundamentals of Database 3 Biniyam TADELE ZEWEDE M 23 Ethiopian 3 Systems CoSc2041 5 20/4/2020 B Fundamentals of Database 4 Tomas ENDALAMAW SNSHAW M 19 Ethiopian 3 Systems CoSc2041 5 20/4/2020 C 4 Tomas ENDALAMAW SNSHAW M 19 Ethiopian 4 Advanced Database Systems CoSc2042 4 19/7/2020 B Advanced Computer 4 Tomas ENDALAMAW SNSHAW M 19 Ethiopian 2 Programming CoSc2031 5 20/4/2020 A 32 Database Management Systems  The above student relation in the first Normal form  STUDENT (StudentId, CourseId ,StuName, StuFname, StuGFName, Gender, Age, Nationality,CourseTitle, CourseCode, CrdtHrs, DateofEnrollment, Grade) Student Table , it is not in the second normal form because except DateofEnrollment and Grade attributes all other attributes are partially dependent on the composite primary key (StudentId, CourseId ) After normalization, three tables in the second normal form results  STUDENT(StudentId,StuName, StuFname, StuGFName, Gender, Age, Nationality)  COURSE(CourseId,CourseTitle, CourseCode, CrdtHrs)  StuEnroll(StudentId, CourseId, DateofEnrollment, Grade) 33 Database Management Systems  Third Normal Form (3NF) A table is said to be in the Third Normal Form when,  It is in the Second Normal form.  And, it doesn't have Transitive Dependency.  Third normal form is based on the notion of transitive dependency  If A, B, and C are attributes of relation R, such that A → B, and B → C, then C is transitively dependent on A  Transitive dependency exists when a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key.  To remove Transitive dependency, we can divide the table, remove the attribute which is causing Transitive dependency, and move it to some other table where it fits in well.  Applying 3NF virtually eliminates all the redundancies.  The goal of the third normal form is to ensure referential integrity 34 Database Management Systems  Example Employee( emp_id ,other attributes, dept_id, jobid,deptmgr) deptmgr is dependent on dept_id which is non prime hence it does fails to satisfy 3NF but decomposing the employee table makes it satisfy 3NF Employee( emp_id ,other attributes, dept_id, job_id) Department (dept_id, deptmgr) 35 Database Management Systems  BCNF (Boyce-Codd Normal Form)  Even when a database is in 3rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key.  It is an advance version of 3NF and it is also referred as 3.5NF. BCNF is stricter than 3NF.  If a table contains only one candidate key, the 3NF and the BCNF are equivalent.  A table complies with BCNF if it is in 3NF and for every functional dependency A->B, A should be the super key of the table.  For a table to be in BCNF with a prime attribute B to have a dependency A → B, A cannot be a non-prime attribute. 36 Database Management Systems   Example 1.Let's assume there is a company where employees work in more than one department. EMPLOYEE (EMP_ID,EMP_COUNTRY,EMP_DEPT,DEPT_TYPE,EMP_DEPT_NO) with PK {EMP-ID, EMP-DEPT} In the above table Functional dependencies are as follows: EMP_ID → EMP_COUNTRY EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}  The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.  To convert the given table into BCNF, we decompose it into three tables:  EMP_COUNTRY (EMP_ID,EMP_COUNTRY)  EMP_DEPT (EMP_DEPT,DEPT_TYPE,EMP_DEPT_NO)  EMP_DEPT_MAPPING (EMP_ID, EMP_DEPT) 37 Database Management Systems  If we assume that every instructor teaches only one course {Stud_id,Course} instructor instructor course but instructor is not a prime attribute hence do not satisfy BCNF Decompose the table into the following to tables to satisfy BCNF student(stud_id,inst_id) snstructor(inst_id,instructor,course) 38 Database Management Systems   4NF (Fourth Normal Form) Rules A table is said to be in the Fourth Normal Form when,  It is in the BCNF Normal form.  And, it doesn't have multi-valued dependency.  a table have multi-valued dependency if the following conditions are true table should have at-least 3 columns(A,B,C) For a dependency A > B, for a single value of A, multiple value of B exists, if there is a multi-valued dependency between, A and B, then B and C should be independent of each other. Example Employee( emp_id ,emp_tel,emp_hobby) 39 Database Management Systems  5NF (Fifth Normal Form) Rules The 5NF (Fifth Normal Form) is also known as project-join normal form  A table is in 5th Normal Form if it should be in 4NF Should not have join dependency 40 Database Management Systems   If join dependency exists decomposing a table and joining the smaller tables results the same data where as either data is lost or new entries created if join dependency does not exist.  A table in 5NF won’t have lossless decomposition into smaller tables means it cannot be decomposed into any number of smaller tables without loss of data.  You can also consider that a relation is in 5NF, if the candidate key implies every join dependency in it. Note :Two important properties of decomposition(breaking a table) Lossless-join property enables us to find any instance of the original relation from corresponding instances in the smaller relations. Dependency preservation property enables us to enforce a constraint on the original relation by enforcing some constraint on each of the smaller relations. 41 Database Management Systems  Example SPC(supplier,product,customer) Breaking in to sp(supplier,product) sc(supplier,customer) pc(customer, product) The result from these join does not guarantee the exact record in the original table since a supplier can supply different products for different customers hence can not decompose the table But if we assume that one supplier only supplies one product for a customer SPC(supplier,product,customer) can break down into sp(supplier,product) sc(supplier,customer) Note If decomposing results information creation or information lost stick to the original table But if decomposing does not result information loss decompose the tables 42 Database Management Systems 43 Database Management Systems