database management system chapter three

relational and non relational database design

1. Chapter Three Database Designs Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

2. Chapter Three : Database Design Relational and non-relational data base design Relational constraint/integrity rule Key constraint Relational view Instance , schema ER Model and diagram Mapping ER into Relational Tables Relational algebra and calculus concepts Relational algebra operators Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

3. Relational and Non-relational data base design What is a relational database? Relational and non-relational databases are used by businesses to store, retrieve, and analyze data. A  relational database stores data in tables composed of rows and columns.  In a relational database, data is contained within a table, which is then linked to data contained within other tables through the use of unique identifying keys. Specifically , relationships between tables are formed when a primary key, which uniquely identifies a row in one table, connects with a foreign key identifying a row of data in another table.  Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

4. Relational and Non-relational data base design What is a relational database? In effect, relational databases are designed to store structured data or well-defined data like names, dates, and quantities that can be easily standardized within a table.  SQL , or Structured Query Language, is the most common programming language used to interface with relational databases within relational database management systems (RDMS).  Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

5. Relational and Non-relational data base design What is a relational database? Relational databases are suitable for storing, retrieving, and manipulating well-defined, structured data. They are easy to use. Some of the most common relational databases include : MySQL IBM Db2 Snowflake Amazon Aurora PostgreSQL Microsoft SQL Server Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

6. Relational and Non-relational data base design What is a non-relational database? A non-relational database is a type of database that doesn’t store data in tables but instead in whatever format is best for the type of data being stored. In effect, non-relational databases are designed to contain unstructured data, or loosely defined data like email messages, videos, images, and business documents that aren’t easily standardized. They can also be used to store a mix of structured and unstructured data.  Non-relational databases are said to be NoSQL, meaning that they don’t use Structured Query Language, even though many NoSQL databases do support SQL queries.  Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

7. Relational and Non-relational data base design Types of non-relational databases Unlike relational databases, which only support a tabular design, there are many different types of non-relational databases. Some of the most common approaches to organizing non-relational databases include : Key-value stores:  In a key-value store, data is assigned a unique identifier, which allows it to be retrieved and sorted. The system consists of "keys," or unique identifiers like a string of numbers, and the "values" with which they're associated, which can be either data itself or simply its location within the database. Column-family data stores:  In a column-family data store, data is organized in a " key space " containing multiple families of different columns. The column families, in turn, include rows that each also contain columns containing data. The data can be accessed and identified using keys. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

8. Relational and Non-relational data base design Types of non-relational databases Unlike relational databases, which only support a tabular design, there are many different types of non-relational databases. Some of the most common approaches to organizing non-relational databases include : Graph databases:  Graph databases store data in nodes and structures them based on their relationships to one another, allowing for a much more flexible schema than can be found in tabular designs. Document databases:  Document databases store data within documents, which typically contain one object and all its associated metadata. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

9. Relational and Non-relational data base design Common non-relational databases Some of the most common non-relational databases include:  MongoDB IBM Cloundant Amazon DynamoDB Apache Cassandra  Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

10. Relational Constraint/Integrity rule Integrity constraints are a set of rules. It is used to maintain the quality of information. These are the restrictions or sets of rules imposed on the database contents. It validates the quality of the database. It validates the various operations like data insertion, updating, and other processes that have to be performed without affecting the integrity of the data. It protects us against threats/damages to the database. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

11. Relational Constraint/Integrity rule Integrity constraints are a set of rules. It is used to maintain the quality of information. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

12. Relational Constraint/Integrity rule Domain constraints Domain constraints can be defined as the definition of a valid set of values for an attribute . No value of the attribute should be beyond the allowable limits . Every domain must contain atomic values(smallest indivisible units) which means composite and multi-valued attributes are not allowed. The data type of domain includes string, character, integer, time, date, currency, etc. The value of the attribute must be available in the corresponding domain. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU EID       Name                   Phone                  01 Bekele Kebede 123456789 234456678

13. Relational Constraint/Integrity rule Entity I ntegrity constraints The entity integrity constraint states that primary key value can't be null. This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can't identify those rows. A table can contain a null value other than the primary key field. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

14. Key Constraints Referential Integrity Constraints A referential integrity constraint is specified between two tables. In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be available in Table 2. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

15. Key Constraints Keys are the entity set that is used to identify an entity within its entity set uniquely. An entity set can have multiple keys, but out of which one key will be the primary key. A primary key can contain a unique and null value in the relational table. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

16. Key Constraints Key Types Primary key : It is the first key used to identify one and only one instance of an entity uniquely. Candidate key: A candidate key is an attribute or set of attributes that can uniquely identify a tuple. Except for the primary key, the remaining attributes are considered a candidate key. The candidate keys are as strong as the primary key . Foreign key : Foreign keys are the column of the table used to point to the primary key of another table. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

17. Key Constraints Super Key : A super key is a set of one or more attributes (columns) that, taken collectively, uniquely identifies each tuple (row) in a relation (table) within a relational database. In other words, it's a combination of attributes that guarantees uniqueness for each row in a table . The primary key is a specific type of super key: a minimal super key, meaning it's the smallest possible set of attributes that uniquely identifies each tuple. However, not all super keys are chosen to be primary keys. In practice, one super key is typically selected to be the primary key for a relation, and the other super keys become alternate keys or candidate keys. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

18. Cont. Alternate key : There may be one or more attributes or a combination of attributes that uniquely identify each tuple in a relation. These attributes or combinations of the attributes are called the candidate keys. One key is chosen as the primary key from these candidate keys, and the remaining candidate key, if it exists, is termed the alternate key. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

19. Relational Views There are two kinds of relation in relational database. 1 . Base Relation(Named Relation) Corresponding to an entity in the conceptual schema, whose tuples are physically stored in the database. 2 . View (Unnamed Relation) A View is the dynamic result of one or more relational operations operating on the base relations to produce another virtual relation that does not actually exist as presented. View is a virtually derived relation that does not necessarily exist in the database but can be produced upon request by a particular user at the time of request. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

20. Purpose of a view Hides unnecessary information from users: since only part of the base relation (Some collection of attributes, not necessarily all) are to be included in the virtual table Provide powerful flexibility and security: since unnecessary information will be hidden from the user there will be some sort of data security . Provide customized view of the database for users: each user is going to be interfaced with his own preferred data set and format by making use of the Views . Update on views derived from various relations is not allowed since it may violate the integrity of the database. Since aggregation and summary results are computed from a base relation and does not exist actually. Update on view with aggregation and summary is not allowed. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

21. ERD – Participation Constraints Participation Constraints The participation constraint specifies the number of instances of an entity can participate in a relationship set Total Participation The Participation of an entity set E in a relationship set R is said to be total if every entity in E participates in at least one relationship in R. The participation of entity set A in the relationship set is total because every entity of A participates in the relationship set. The participation of entity set B in the relationship set is also total because every entity of B also participates in the relationship set Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

22. ERD – Participation Constraints Partial Participation The participation of an entity set E in relationship set R is said to be partial if only some entities in E participate in relationships in R. The participation of entity set A in the relationship set is partial because only some entities of A participate in the relationship set. while The participation of entity set B in the relationship set is total because every entity of B participates in the relationship set . In ERD, the total participation is denoted by doubled-line between entity set and relationship set and partial participation is denoted by single line between entity set and relationship set. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

23. ERD – Participation Constraints Example Suppose an entity set Student related to an entity set Course through Enrolled relationship set. The participation of entity set course in enrolled relationship set is partial because a course may or may not have students enrolled in. It is possible that only some of the course entities are related to the student entity set through the enrolled relationship set. The participation of entity set student in enrolled relationship set is total because every student is expect to relate at least one course through the enrolled relationship set Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

24. ERD Examples Example 1: Build an ER Diagram for the following A student record management system will have the following two basic data object categories with their own features and properties Student will have an Id, Name, Department , Age , GPA, and Course will have code, name Credit hours Whenever a student enroll in a course in a specific Academic year, and Semester the student have a grade for the course Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

25. ERD Examples Example 2: Build an ER Diagram for the following A employee record management system will have the following two basic data object categories with their own features and properties Employee will have an Id, Name, DoB , Age , Tel, and Department will have Id, name and Location Whenever an employee enroll in a one Department the duration of his/her stay in the respective department should be registered Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

26. ERD Examples Example 3 : Build an ER Diagram for the following A simplified requirements of tiny college are as follows: A college contains many departments. Each department can offer any number of courses Many instructors can work in a departments An instructor can work only in one department For each department there is a head An instructor can be head of only one department Each instructor can offer any number of courses A course can be offered by only one instructor A student can enroll for any number of courses Each course can be enrolled any number of students Construct ER diagram and describe any assumption you take. Show the min. and max. cardinalities. State the degree of relation ship between entities. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

27. ERD Examples Example 3 : Build an ER Diagram for the following ER Model and diagram Mapping ER into Relational Tables Relational algebra and calculus concepts Relational algebra operators Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

28. ER Model and diagram The Entity Relational Model is a model for identifying entities to be represented in the database and representation of how those entities are related. The ER data model specifies enterprise schema that represents the overall logical structure of a database graphically.  The Entity Relationship Diagram explains the relationship among the entities present in the database. ER models are used to model real-world objects like a person, a car, or a company and the relation between these real-world objects. In short, the ER Diagram is the structural format of the database. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

29. ER Model and diagram Why Use ER Diagrams In DBMS? ER diagrams are used to represent the E-R model in a database, which makes them easy to convert into relations (tables). ER diagrams provide the purpose of real-world modeling of objects which makes them intently useful. ER diagrams require no technical knowledge and no hardware support. These diagrams are very easy to understand and easy to create even for a naive user.  It gives a standard solution for visualizing the data logically. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

30. ER Model and diagram Symbols Used in ER Model ER Model is used to model the logical view of the system from a data perspective which consists of these symbols: Rectangles: Rectangles represent Entities in the ER Model. Ellipses: Ellipses represent Attributes in the ER Model. Diamond: Diamonds represent Relationships among Entities. Lines: Lines represent attributes to entities and entity sets with other relationship types. Double Ellipse: Double Ellipses represent Multi-Valued Attributes. Double Rectangle: Double Rectangle represents a Weak Entity. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

31. ER Model and diagram Components of ER Diagram ER Model consists of Entities, Attributes, and Relationships among Entities in a Database System. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

32. Mapping ER into Relational Tables Example 1 : Convert ERD Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

33. Mapping ER into Relational Tables Example 1 : Convert ERD The Member relation does not have a composite attribute  name . Since Borrows is a many-to-many relationship the Borrow relation is defined with a composite primary key { memberId ,  bookId ,  dateTimeBorrowed }. memberId  in the Borrow relation is a foreign key referencing Member. bookId   in the Borrow relation is a foreign key referencing Book. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

34. Mapping ER into Relational Tables Example 2 : Convert ERD The attribute  supervisor  is a foreign key referencing Employee. A foreign key is placed on the ‘many’ side of a relationship and so in this case the foreign key references the employee who is the supervisor (the role name on the ‘one’ side); hence the name  supervisor  was chosen as the attribute name Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

35. Mapping ER into Relational Tables Example 3 : Convert ERD deptCode   was chosen as the primary key of Department. deptName   is a key and so a unique index can be defined to ensure uniqueness. Since Course is a weak entity type and is involved in an identifying relationship, the primary key of Course is composite comprising { deptCode ,  courseNo }. deptCode   in Course is a foreign key referencing Department. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

36. Mapping ER into Relational Tables Exercises : Convert ERD Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

37. Mapping ER into Relational Tables Exercises : Convert ERD Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

38. Mapping ER into Relational Tables Exercises : Convert ERD Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

39. Relational Algebra Relational algebra and calculus are fundamental concepts in database theory and management Relational Algebra: Selection (σ): Selects rows from a relation that satisfy a given condition. Projection (π): Selects specific columns from a relation. Union (∪): Combines tuples from two relations, removing duplicates. Intersection (∩): Returns tuples common to two relations. Difference (-): Returns tuples from one relation not found in another. Cartesian Product (×): Combines all possible pairs of tuples from two relations. Join (⨝): Combines tuples from two relations based on a common attribute. Division (÷): Returns tuples from one relation that are related to all tuples in another relation. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

40. Relational Algebra and Calculus Concepts Selection (σ): Select Operator is denoted by sigma (σ) and it is used to find the tuples (or rows) in a relation (or table) which satisfy the given condition Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU σ Condition / Predicate ( Relation / Table Name)

41. Relational Algebra and Calculus Concepts Relational Calculus: Let's say we have a simple relation called "Employees" with attributes like "Name", "Age", and "Department ". Now, let's say we want to select rows from this relation where employees are older than 30. We can use the selection operator (σ) for this purpose. σ( Age > 30)(Employees) Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU Name Age Department Gebreyes 30 Electrical Yonas 29 Computer Ashenasfi 35 Mechanical Chere 33 Software Name Age Department Ashenasfi 35 Mechanical Chere 33 Software

42. Relational Algebra and Calculus Concepts Projection (π): Project operator is denoted by ∏ symbol and it is used to select desired columns (or attributes) from a table (or relation). Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU ∏ column_name1, column_name2, ...., column_nameN ( table_name )

43. Relational Algebra and Calculus Concepts Projection (π): Let's say we have a simple relation called "Employees" with attributes like "Name", "Age", and "Department ". Now, let's say we want to select only name and department. Π Name , Department (Employees ) Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU Name Age Department Gebreyes 30 Electrical Yonas 29 Computer Ashenasfi 35 Mechanical Chere 33 Software Name Department Gebreyes Electrical Yonas Computer Ashenasfi Mechanical Chere Software

44. Relational Algebra and Calculus Concepts Write Relation algebra for the following SQL statement SELECT name, department FROM Employee WHERE age > 30; π name , department (σ age >30 ( Employee )) Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

45. Relational Algebra and Calculus Concepts Write Relation algebra for the following SQL statement SELECT name, department FROM Employee WHERE age > 30 AND Department=‘ Elettical ’; π name, department​ (σ age>30 ∧ department =′electrical′​(Employee)) Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

46. Relational Algebra and Calculus Concepts Union (∪ ) Union operator is denoted by ∪ symbol and it is used to select all the rows (tuples) from two tables (relations). Lets say we have two relations R1 and R2 both have same columns and we want to select all the tuples(rows) from these relations then we can apply the union operator on these relations. Note:  The rows (tuples) that are present in both the tables will only appear once in the union set. In short you can say that there are no duplicates present after the union operation. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

47. Relational Algebra and Calculus Concepts Union (∪ ) Note:  As you can see there are no duplicate names present in the output even though we had few common names in both the tables, also in the COURSE table we had the duplicate name itself. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU table_name1 ∪ table_name2

48. Relational Algebra and Calculus Concepts Intersection Operator (∩) Intersection operator is denoted by ∩ symbol and it is used to select common rows (tuples) from two tables (relations). Lets say we have two relations R1 and R2 both have same columns and we want to select all those tuples(rows) that are present in both the relations, then in that case we can apply intersection operation on these two relations R1 ∩ R2 . Note:  Only those rows that are present in both the tables will appear in the result set. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

49. Relational Algebra and Calculus Concepts Intersection Operator (∩) Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU table_name1 ∩ table_name2

50. Relational Algebra and Calculus Concepts Set Difference (-) Set Difference is denoted by – symbol. Lets say we have two relations R1 and R2 and we want to select all those tuples(rows) that are present in Relation R1 but not present in Relation R2, this can be done using Set difference R1 – R2. Query: Lets write a query to select those student names that are present in STUDENT table but not present in COURSE table Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU table_name1 - table_name2

51. Relational Algebra and Calculus Concepts Set Difference (-) Set Difference is denoted by – symbol. Lets say we have two relations R1 and R2 and we want to select all those tuples(rows) that are present in Relation R1 but not present in Relation R2, this can be done using Set difference R1 – R2. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU table_name1 - table_name2

52. Relational Algebra and Calculus Concepts Set Difference (-) Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU table_name1 - table_name2

53. Relational Algebra and Calculus Concepts Cartesian Product (×): Cartesian Product is denoted by X symbol. Lets say we have two relations R1 and R2 then the Cartesian product of these two relations (R1 X R2) would combine each tuple of first relation R1 with the each tuple of second relation R2. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

54. Relational Algebra and Calculus Concepts Cartesian Product (×): In relational algebra, the Cartesian product is a binary operation that combines two relations (tables) by pairing each tuple (row) of the first relation with every tuple of the second relation. It's denoted by the symbol ×. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU A B 1 x 2 y C D 3 z 4 w A B C D 1 x 3 z 1 x 4 w 2 y 3 z 2 y 4 w

55. Relational Algebra and Calculus Concepts Join (⨝): Combines tuples from two relations based on a common attribute Join is an operation in DBMS(Database Management System) that combines the row of two or more tables based on related columns between them. The main purpose of Join is to retrieve the data from multiple tables in other words Join is used to perform multi-table query. It is denoted by ⨝. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

56. Relational Algebra and Calculus Concepts Types of Joins in Relational Algebra Employee Table Department Table Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU emp_id name age dept_id 1 Alice 30 101 2 Bob 25 102 3 Charlie 35 101 dept_id dept_name 101 Electrical 102 Mechanical 103 Civil

57. Relational Algebra and Calculus Concepts Types of Joins in Relational Algebra Inner Join Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

58. Relational Algebra and Calculus Concepts Types of Joins in Relational Algebra Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

59. Relational Algebra and Calculus Concepts Types of Joins in Relational Algebra Outer Join Outer join is a type of join that retrieve matching as well as non- maching records from related tables. There three types of outer join Left outer join Right outer join Full outer join Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

60. Relational Algebra and Calculus Concepts Types of Joins in Relational Algebra Left Outer Join : It is also called left join. This type of outer join retrieve all records from left table and retrieve maching record from right table Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

61. Relational Algebra and Calculus Concepts Types of Joins in Relational Algebra Right Outer Join: It is also called right join. This type of outer join retrieve all records from right table and retrieve maching record from right table. Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

62. Relational Algebra and Calculus Concepts Types of Joins in Relational Algebra Full Outer Join: In full outer join all the rows from both table are inserted in result table Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU

63. Relational Algebra and Calculus Concepts Reading Assignment Relational Calculus Concepts Prepared by Befkadu (MSc) : 2016 E.C 23/24 A.C in AASTU