Database Concepts PDF

Southern Technical University Basra Management Technical College Information Technology Dept. Prepared by: Asst. Lec. Ismaeil Aziz Bahar Weeks 4, 5 and 6 1 Relational Model Concepts 2  1-...

Southern Technical University Basra Management Technical College Information Technology Dept. Prepared by: Asst. Lec. Ismaeil Aziz Bahar Weeks 4, 5 and 6 1 Relational Model Concepts 2  1- RDBMS  RDBMS stands for Relational Database Management System. It is the basis for all modern database systems (DBMS) like IBM DB2, Oracle, MS SQL Server, Microsoft Relational Access, and MySQL.  The Relational Model in DBMS was coined in the year 1970 and was proposed by E.F Codd for IBM. Model  The data relational model is totally based on the related concept. A relation is nothing but it is a mathematical model that is basically based on the ideas of the sets.  RDBMS is nothing but it is a way to represent how large data is being stored Concepts in the relational databases. The data are stored in the form of relations or tables in the relational database.  Firstly the designing of the conceptual model of the database is done through ER diagram, and after that, it needs to be converted into the Relational Model in DBMS, which further can be implemented by using any of the RDBMS i.e., Relational Database Management System languages. 3  2- The 10 Concepts of Relational Model in RDBMS 1. Relation: A relation is a table which is a combination of rows or tuples and Relational columns 2. Attributes: An attribute represent the unique name for each column so that a particular column gets the identity. For example, in the above student table, Model there were 3 columns and thus 3 attributes and that are Roll No, Name, and Phone. 3. Domain: A domain tells which type of value can be stored in a particular column, Concepts in the Roll No column it will always accept the integer value, but it won’t accept the string value. So, the domain in Relational Model in DBMS is also defined as the set of allowable values for all the attributes in the tables. 4. Tuples: Tuples represent the rows of a relation. 5. Relation Schema: The name of the relations with its attributes is represented by the relation schema. 4  2- The 10 Concepts of Relational Model in RDBMS 6. Relation Instance (State): Relation Instance makes sure that there are no duplicate rows present in the table. So, it is nothing but it is a finite set of rows or tuples. Relational 7. Degree: Degree is represented by the total number of attributes or columns present in the relation or table. In the below student table, the degree is 3, a total of 3 columns are present and that are Roll No, Name, and Phone. Model 8. Cardinality: Cardinality is just the opposite of the degree, it is represented by the total number of rows present in the relation or table. In the below student table, the Concepts cardinality is 5, and a total of 5 rows is present (the number of elements in the set). 9. Relation Key: The relation key in Relational Model in DBMS is also known as the primary key, It is used to uniquely identify each row in the table. 10. Tuple Variable: Tuple Variable represents all the data that are stored in a record of the table or relation. Like in the below student table, any value of the row will be considered as the tuple variable. For example, Hana is the tuple variable. 5  2- The 10 Concepts of Relational Model in RDBMS Relational Model Concepts 6 Types of relations 7  1- Relationships  Relationships are meaningful associations between tables that contain related information — they’re what make databases useful. Without some connection Types between tables in a database, you may as well be working with disparate spreadsheet files rather than a database system.  As we covered in our short overview of databases, databases are collections of of tables, and those tables have fields (also known as columns). Every table contains a field known as an entity (or primary) key, which identifies the rows within that table. By telling your database that the key values in one relations table correspond to key values in another, you create a relationship between those tables; these relationships make it possible to run powerful queries across different tables in your database. When one table’s entity key gets linked to a second table, it’s known as a foreign key in that second table. 8  1- Relationships  Identifying the connections you’ll need between tables is part of the data Types modeling and schema design process — that is, the process of figuring out how your data fits together, and how exactly you should configure your tables and their fields. This process often involves creating a visual representation of tables of and their relationships, known an entity relationship diagram (ERD), with different notations specifying the kinds of relationships. Those relationships between your tables can be: relations One-to-one One-to-many Many-to-many 9  1.1- one-to-one relationship  In a one-to-one relationship (1:1) , each record in Table A can have only one matching record in Table B, and each record in Table B can have only one Types matching record in Table A. This type of relationship is not common, because most information related in this way would be in one table. of relations 10  1.1- one-to-one relationship Why not add all columns to a single table? One Reason: Many optional attributes that only apply to some records (Less storage). Types optional attributes Some user have and some not of relations It will be converted to the tables on the next page 11  1.1- one-to-one relationship converted tables Types of relations Another Reason:  Data loaded from several places can not combine all data into single table.  Isolate part of a table for security reasons. 12  1.2- one-to-many relationship  A one-to-many relationship (1:m), often referred to as a "master-detail" or "parent-child" relationship.  A one-to-many relationship is the most common type of relationship. In a one- Types to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.  One-to-Many relationships can also be viewed as Many-to-One relationships, depending on which way you look at it. of Example : Books and Author Book_ID Book_Title Book_pages Author_Name Year 1997 relations 1 C++ Programming Language 923 Bjarne Stroustrup 2 A Brief Look at C++ 320 Bjarne Stroustrup 1997 3 The C 238 Kernighan 2009 Programming Language in the Next page we will describe this as a One to Many Relationship, where a book Title belongs to an Author and an author has many book title. 13  1.2- one-to-many relationship Books Book_ID Book_Title Book_pages Types 1 C++ Programming Language 923 We will divide the previous 2 A Brief Look at C++ 320 of table into two tables: books and author , This process is called normalization. 3 The C Programming 238 Language relations Authors Author_ID Author_Name Year 1 Bjarne Stroustrup 1997 2 Kernighan 2009 14  1.2- one-to-many relationship Foreign keys Books Book_ID Book_Title Book_pages Author_ID 1 C++ Programming 923 1 Types 2 Language A Brief Look at C++ 320 1 of 3 The C Programming 238 Language Authors 2 Author_ID Author_Name Year relations primary keys 1 Bjarne Stroustrup 1997 2 Kernighan 2009 When we normalize our database, we need to include some sort of marker for ourselves to know how to relate the tables to one another. We do this with primary keys and foreign keys. 15  1.3- many-to-many relationship  In a many-to-many relationship (n:m), a record in Table A can have many matching records in Table B, and a record in Table B can have many matching Types records in Table A.  This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields the foreign keys from of both Tables A and B.  A many-to-many relationship is really two one-to-many relationships with a third table.  Example: Students enroll in classes relations Suppose we have the two tables as on the next page, the student table in the left and the class table in the right, they both have an ID to identify row and name such as student name and class name. We have this facts: Every student have many Classes. Every Class have many students. How can such a relationships be made? 16  1.3- many-to-many relationship Types of Normally when we want to relate two tables together we put primary key of one into other table as foreign Key relations 17  1.3- many-to-many relationship Types of Making such fields would break the following rules: 1-foreign key should have single value, and it permits only those values which relations appear in the primary key of the table to which it refers. 2-The field type of Student ID and Class ID, is not defined in any way, and this affects the integrity of the database 3-Deleting And Updating the student must be done in both tables, otherwise there will be inconsistency in the database. 4-It is not possible to add any student without specifying the class, and it is also not possible to add any class without specifying the students. 18  1.3- many-to-many relationship  The solution is to create a third table, which we call a joining table Types of relations 19  1.3- many-to-many relationship Types of relations 20  1.3- many-to-many relationship We can add an ID field to the Joining table (student_class) Types of relations 21  1.5- Real Example many-to-many relationship We can add also any data related to the enrollment process: Types of relations 22  1.3- many-to-many relationship We can rename the joining table to suit its function Suggested names for the joining table : Types of relations 23 Normalization Forms 24  1- Definitions Normalization is the process of organizing data in a database. It includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency. Types of Normalization Normalization 1. 2. 3. 1NF (First Normal Form) 2NF (Second Normal Form) 3NF (Third Normal Form) 4. 5. 6. BCNF (Boyce-Codd Normal Form) 4NF (Fourth Normal Form) 5NF (Fifth Normal Form) Forms 25  1- Definitions A Full functional dependency is a one-way relationship between two attributes, such that at any given time, for each unique value of attribute A, only one value of attribute B is associated with it throughout the relation. A→B Normalization A Partial dependency is a situation in which a non-key attribute of a table depends on only a part of the primary key. In other words, the value of the non-key attribute Forms can be determined by a subset of the primary key but not the entire key. A → B,C A transitive dependency refers to some non-prime attribute other than the candidate key that depends on another non-prime attribute that is dependent entirely on the candidate key.  A → B Full functional dependency exists.  There is no B → A functional dependency.  B → C Full functional dependency exists. 26  2- Data Anomalies What is Anomaly? Anomalies in the relational model refer to inconsistencies or errors that can arise when working with relational databases, specifically in the context of data insertion, deletion, and modification. The main reason for the database nomalies is that all the data is stored in a single table. So, to remove the anomalies of the Normalization database, normalization is the process which is done where the splitting of the table and joining of the table occurs. Forms These anomalies can be categorized into three types: 1. Insertion Anomalies. 2. Deletion Anomalies. 3. Update Anomalies. Example: In the table on the next page, we have listed students with their name, id and Their respective branch. 27  2- Data Anomalies 1- Update Anomaly Student_id Student_name Student_branch if Mohammed changes his branch from 1 Mohammed Computer science Computer Science to Electronics, then 1 Mohammed Computer science we will have to update all the rows. 2 Zaid Electronics If we miss any row, then Mohammed will Normalization have more than one branch, which will 3 3 Moussa Moussa Electrical Electrical create the update anomaly in the table. Forms 2- Insertion Anomaly 4 Jamal Mechanical If we add IT as a new branch, which at the present time does not have any students we cannot insert the row into the table without id and name. This is called insertion anomaly. 3-Deletion Anomaly If we remove the Mechanical from the table, then we will have to delete its row from the table. But it will also delete the table of Jamal and his details. So, this is called deletion anomaly and it will make the database inconsistent. 28  3- 1NF (First Normal Form) Step 1: A table is in first normal form (1NF) if and only if all columns contain only atomic values—that is, each column can have only one value for each row in the table. Student Name Address Class Class Hours Lecturer Lecturer Department Grades No. No. Name No. Name Normalization 5 Hamed Basrah IT101 IT102 Design C++ 3 4 7 12 Mohammed Ali IT CS B+ A IT103 DB 4 15 Ferida CS A Forms 6 Saeed Thiqar IT102 IT512 C++ Network 4 5 12 9 Ali Hamza CS IT B+ C Student Name Address Class Class Hours Lecturer Lecturer Department Grades No. No. Name No. Name 5 Hamed Basrah IT101 Design 3 7 Mohammed IT B+ 5 Hamed Basrah IT102 C++ 4 12 Ali CS A 5 Hamed Basrah IT103 DB 4 15 Ferida CS A 6 Saeed Thiqar IT102 C++ 4 12 Ali CS B+ 6 Saeed Thiqar IT512 Network 5 9 Hamza IT C 29  3- 1NF (First Normal Form) Student Name Address Class Class Hours Lecturer Lecturer Department Grades No. No. Name No. Name 5 Hamed Basrah IT101 Design 3 7 Mohammed IT B+ 5 Hamed Basrah IT102 C++ 4 9 Ali CS A+ 5 Hamed Basrah IT103 DB 4 12 Hamza IT A 6 Saeed Thiqar IT102 C++ 4 9 Ali CS B Normalization 6 Saeed 1-Student (1NF) Thiqar Step 2: IT512 Network 5 12 Hamza IT C Student Name Address 1. Select the primary key (PK) Forms No. (PK) 5 Hamed Basrah 2. Select fields that have Full functional dependency for the candidate primary key: Address, Name → Student NO 6 Saeed Thiqar Class Name, Hour → Class NO Lecturer Name, Department → Lecture No 3. Split the table into tables according to the specified primary key 2-Class (1NF) and its dependent fields Student Class Lecturer Grades Class Class Hours 3-Lecturer (1NF) No. (FK) No. (FK) No. (FK) No.(PK) Name Lecturer Lecturer Department 5 IT101 7 B+ IT101 Design 3 No. (PK) Name 5 IT102 9 A+ IT102 C++ 4 7 Mohammed IT 5 IT103 12 A IT103 DB 4 9 Ali CS 6 IT102 9 B IT512 Network 5 12 Hamza IT 6 IT512 12 C 30  4- 2NF (First Normal Form) 1-Student (1NF) 3-Lecturer (1NF) No name(1NF) Student Name Address Lecturer Lecturer Department Student Class Lecturer Grades No. (PK) No. (PK) Name No.(FK) No.(FK) No. (FK) 5 Hamed Basrah 7 Mohammed IT 5 IT101 7 B+ 6 Saeed Thiqar 9 Ali CS 5 IT102 9 A+ 12 Hamza IT 5 IT103 12 A Normalization 2-Class (1NF) Class Class Hours 2NF Steps : 6 6 IT102 IT512 9 12 B C No. (PK) Name IT101 Design 3 1. All tables must be passed the first normal form (1NF). Forms IT102 IT103 C++ DB 4 4 2. In the table with no name, try to find the partial dependency: Grades → Student NO , Class N0 IT512 Network 5 3. Split the nameless table into two tables, both tables must have two foreign keys. 4-Student_Class (2NF) Student Class Grades Student Class Lecturer No. (FK) No. (FK) No. (FK) No. (FK) No. 5 IT101 B+ 5 IT101 7 5 IT102 A+ 5 IT102 9 5 IT103 A 5 IT103 12 6 IT102 B 6 IT102 9 6 IT512 12 6 IT512 C 31  5- 3NF (First Normal Form) No name(2NF) 1-Student (1NF) 3-Lecturer (1NF) Student Class Lecturer Lecturer Lecturer Department No. (FK) No.(FK) No.(FK) Student Name Address No. (PK) Name 5 IT101 7 No. (PK) 5 Hamed Basrah 7 Mohammed IT 5 IT102 9 6 Saeed Thiqar 9 Ali CS 5 IT103 12 12 Hamza IT 6 IT102 9 6 IT512 12 Normalization 2-Class (1NF) 2-Class (3NF) Class Class Hour Class Class Hours Lecturer No.(PK) Name No.(PK) Name No.(FK) Forms IT101 IT102 Design C++ 3 4 IT101 IT102 Design C++ 3 4 7 9 IT103 DB 4 IT103 DB 4 12 IT512 Network 5 3NF Steps : IT512 Network 5 9 4-Student_Class (2NF) 1. All tables must be passed the second normal form (2NF). Student Class Grades 2. In a table with no name, try to find the transitive No. (FK) No. (FK) dependency through its related tables: 5 IT101 B+ Lecture Name → Lecturer No. 5 IT102 A+ Class No → Lecturer No. 5 IT103 A Therefor Class No. No → Lecture Name 6 IT102 B 3. In order for the last relationship to be correct, we add the 6 IT512 C Lecturer No. to the table of Class as foreign key. 32  6- Tables Relationship 1-Student (1NF) 3-Lecturer (1NF) Student Name Address Lecturer Lecturer Department No. (PK) No. (PK) Name 5 Hamed Basrah 7 Mohammed IT 6 Saeed Thiqar 9 Ali CS 12 Hamza IT Normalization 2-Class (3NF) Class No. (PK) Class Name Hours Lecturer No. (FK) IT101 Design 3 7 Forms IT102 IT103 C++ DB 4 4 9 12 Student Class Grades IT512 Network 5 9 No. (FK) No. (FK) 5 IT101 B+ 5 IT102 A+ 5 IT103 A 6 IT102 B 6 IT512 C 4-Student_Class (2NF) 33  7- Exercises Exercise 1: Describe and explain the data normalization process shown in the table below (1Nf, 2Nf, 3nf) and draw the relationships between the normalized tables. Accountant Skill Skill Proficiency Accountant Accountant Group Group Group No. No. Category No. Name Age No. City Supervisor 21 113 Systems 3 Ali 55 52 Basra Baaqr Normalization 35 113 179 Systems Tax 5 1 Dawood 32 44 Baghdad Jalal 204 Audit 6 Forms 50 77 179 148 Tax Consulting 2 6 Kareem Zaid 40 52 44 52 Baghdad Basra Jalal Baaqr 179 Tax 6 34  7- Exercises Exercise 2: Describe and explain the data normalization process shown in the table below (1Nf, 2Nf, 3nf) and draw the relationships between the normalized tables. Employee Employee Employee Work Project Project Project Project Department Department id name phone Hour id name start-date End-date Id name 1 Ali 07732134567 4 2 MC201 1-2-2018 1-5-2018 1 Mechanics Normalization 2 Kareem 07707654312 5 3 7 1 3 2 CIV101 El301 MC201 1-3-2018 2-7-2019 1-2-2018 1-8-2018 2-12-2019 1-5-2018 2 3 1 Civil Electricity Mechanics 5 1 CIV101 1-3-2018 1-8-2018 2 Civil Forms 4 Mustafa 07713568792 8 4 3 1 EL301 CIV101 2-7-2019 1-3-2018 2-12-2019 1-8-2018 3 2 Electricity Civil 35