A database system is an organized collection of related data managed by a Database Management System (DBMS). It ensures data integrity, reduces redundancy, supports multiple users, and enables efficient data storage, retrieval, and manipulation for variou

the first chapter of the database management system

1. Chapter Two Relational Data Model 1

2. Terminologies Relational Constraints Relational Integrity Key constraints Referential constraints Relational languages and views Relational DBMS Outline 2

3. was proposed by Dr. Edgar F. Codd in 1970 at IBM. It became the foundation of modern database systems because of its simplicity, flexibility , and use of mathematical concepts (set theory and first-order predicate logic) all data is logically structured within relations The greatest strength of the relational model is the simple logical structure that it forms A relational database consists of a collection of tables, each having a unique name 3 Relational model

4. Data is stored in relations (tables). Each relation is made up of tuples and attributes The schema defines the structure of a relation (attribute names and data types) The instance is the actual data at a specific time. Operations are performed on relations using relational algebra or SQL 4 Basic concepts

5. Relation:   a relation is a table with columns and rows. Attribute:  an attribute is a named column of a relation ( e.g., Name, Address ) Domain :  a domain is the set of allowable values for one or more attributes Tuple:   a tuple is a row of a relation. Degree : the number of attributes in the relation Cardinality : the number of tuples the relation has Schema: the description of a relation, including its attributes, their domains, and the keys 5 Relational model terminologies

6. Relation Schema : the structure of the relation and represents the name of the relation with its attributes E.g. STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is the relation schema for STUDENT Relation Instance: the set of tuples of a relation at a particular instance of time is called a relation instance It can change whenever there is an insertion, deletion or update in the database 6 Relational model terminologies

7. 7 Cont’d …

8. Data Representation  data stored in rows and columns Atomic Values  e ach cell holds a single, indivisible value . Unique Keys  primary key ensures unique identification of each record . Attribute Domain  e ach attribute has a defined data type and valid range Data Independence  schema changes don’t affect the application layer . Relational Operations  s upports selection, projection, join, union, intersection . Data Consistency  constraints maintain accuracy and reduce redundancy . 8 Characteristics of the Relational Model

9. The building blocks of the relational data model are: Entities : real world physical or logical object Attributes : properties used to describe each entity or real world object Simple Vs Composite attribues Single-valued Vs. multi-valued attributes Stored vs. Derived Attribute Relationship : the association(link, connection) between entities. Constraints : rules that should be obeyed while manipulating the data. 9 Building Blocks of Relational Data Model

10. 1. Simple (Atomic) Vs Composite Attributes Simple : contains a single value (not divided into sub parts). E.g. Age, weight, marriage status Composite: the attributes can be divided into smaller subparts, which represent more basic attributes with independent meaning. E.g. Name (FN,LN) and address ( Kebele , Zone, Wereda,City ) b . Single-valued Vs multi-valued Attributes Single-valued: have only single value (the value may change but has only one value at one time ) E.g . Name, Sex, Id. No . Types of Attributes 10

11. Multi-Valued: having a set of values for the single entity May have lower and upper bounds on the number of values allowed for each individual entity E.g. Address, College Degree, Phone-numbers, Skills c. Stored vs. Derived Attribute Stored : not possible to derive or compute. E.g. Name, Address Derived: the value may be derived (computed) from the values of other attributes. E.g. Age (current year –year of birth), Length of employment (current date- started date), G.P.A (grade point/credit hours ) Types of Attributes … 11

12. Null Values :- applies to attributes which used for don't know or not applicable e.g. Phone number, middle name Default value:- i s a predefined value automatically assigned to a column when no value is provided during data insertion It helps maintain data consistency and reduces the chance of NULL values E.g department =“software” Types of Attributes … 12

13. the type of relationship between two tables in an ERD (either i dentifying or non-identifying ); Identifying Relationship → Strong link ; child fully depends on parent; parent’s key is part of child’s key. E.g. dep’t &employee Non-Identifying Relationship → Weak link; child has its own key; parent’s key only appears as a foreign key. E.g. customer & order The number of entities participating in a relationship is called the DEGREE of the relationship. UNARY/RECURSIVE RELATIONSHIP: Single entity BINARY RELATIONSHIPS: Two entities associated TERNARY RELATIONSHIP: Three entities associated N-NARY RELATIONSHIP: arbitrary number of entities sets Relationship Types 13

14. Cardinality of a Relationship is the number of instances of one entity that can be associated with a single instance of another entity in a relationship . It represents the range of participation between entities are: ONE-TO-ONE, e.g. Building - Location, ONE-TO-MANY , e.g. hospital - patient, MANY-TO-ONE , e.g. Employee - Department MANY-TO-MANY , e.g. Author - Book Relationship Types … 14

15. Primary Key: uniquely identifies each tuple in a relation. It must contain unique values and cannot have NULL values . Candidate Key: is a set of attributes that can uniquely identify a tuple in a relation . It can have N ull value Super Key: is a set of attributes that can uniquely identify a tuple Foreign Key: is a set of attributes in a table that refers to the primary key of another table Composite Key: primary key having two or more attributes is called composite key It is a combination of two or more column Types of Keys in the Relational Model 15

16. Rational Constraints are the rules that force DBMSs to check that data satisfies the semantics are rules that ensure the accuracy, consistency, and validity of data in a database. Domain Integrity: No value of the attribute should be beyond the allowable limits e.g . the Employee ID (EID ) must be unique or employee Birthdate in the range [ Jan 1, 1950, Jan 1, 2000 ] Relational Constraints/Integrity Rules 16

17. Entity Integrity: 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 E.g. Relational Constraints/Integrity Rules 17

18. Referential Integrity: is specified between two relations and is used to maintain the consistency among tuples in the two relations Informally , the referential integrity constraint states that a tuple in one relation that refers to another relation must refer to an existing tuple in that relation E.G . Referential integrity constraint in the customer/order database of the company: Customer( CustID , CustName ) Order( OrderID , CustID , OrderDate ) Relational Constraints/Integrity Rules 18

19. Key Constraints Ensure that each record (row) in a table can be uniquely identified Primary Key: the candidate key that is selected to identify tuples uniquely within the relation . The entire set of attributes in a relation can be considered as a primary key in a worst case. Foreign Key : an attribute, or set of attributes, within one relation that matches the candidate key of some relation. A foreign key is a link between different relations to create the view or the unnamed relation Relational Constraints/Integrity Rules 19

20. Base relation and views 20 Base Relation: A Named Relation corresponding to an entity in the conceptual schema, whose tuples are physically stored in the database. View : i s the dynamic result of one or more relational operations operating on the base relations to produce another virtual relation. So, a view 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

21. Database Languages 21 are specialized sets of commands and instructions used to define, manipulate and control data within a database. Each language type plays a distinct role in database management, ensuring efficient storage, retrieval and security of data

22. Thank You! 22