Introduction to Database Systems, File systems Vs DBMS, Advantages of DBMS Introduction to DBMS Data : It is a RAW FACT (It won't give any meaning ) Ex: 10, RAM, ete. Information : Which gives meaning for Data Ex: id= 10, name RAM" Distance in miles = 200, etc. Database : Collection of meaningfial interrelated information Ex: DB2,ORACLE, SQL Server, MySQL, etc. Database Management System ( DBMS ) : + Database Management Systems (DBMS) are software systems used to store, retrieve, and run queries on data which is stored in a database. + ADBMS serves as an interface between an end-user and a database, allowing users to create, read, update, and delete data in the database. + DBMS manage the data, the database engine, and the database schema, allowing for data to be manipulated or extracted by users and other programs. + This helps provide data security, data integrity, concurrency, and uniform data administration procedures. DBMS Vs File Processing System ‘What is File Processing / Management System? A File Processing / Management system is a DBMS that allows access to single files or tables at a time. Ina File System, data is directly stored in set of files. It contains flat files that have no relation to other files (when only one table is stored in single file, then this file is known as flat file). Limitations of File Processing System ¥ Data redundancy Data inconsistency Data Isolation Data Dependency on application programs Atomicity SN Data Security Advantage of DBMS over file system ¥ No redundant data Data Consistency and IntegrityData Concurrency Data Security Data Privacy Easy access to data Ssyys Data Recovery v Flexible Why use DBMS + To develop software applications In less time. + Data independence and efficient use of data. + For uniform data administration, + For data integrity and security. + For concurrent access to data, and data recovery from crashes. + To use user-friendly declarative query language Database Design and ER Diagrams-Entities, Attributes, And Entity Sets Design Phases The initial phase of database design is to characterize fully the data needs of the prospective database users. Next, the designer chooses a data model and, by applying the concepts of the chosen data model, translates these requirements into a conceptual schema of the database. A fully developed conceptual schema also indicates the functional requirements of the enterprise. In a “specification of functional requirements”, users describe the kinds of operations (or transactions) that will be performed on the data. Design Phases -Types Logical Design ~ Deciding on the database schema. Database design requires that we find a “good” collection of relation schemas. Physical Design ~ Deciding on the physical layout of the database. ER model ~ Database Modeling ‘The ER data mode was developed to facilitate database design by allowing specification of an enterprise schema that represents the overall logical structure of a database. ‘The ER model is very useful in mapping the meanings and interactions of real-world enterprises onto a conceptual schema. Because of this usefulness, many database- design tools draw on concepts from the ER model.+ The ER data mode! employs three basic concepts: + entity sets, + relationship sets, + attributes + The ER model also has an associated diagrammatic representation, the ER diagram, which can express the overall logical structure of a database graphically. Entity Sets + An entity is an object that exists and is distinguishable from other objects. + Example: specific person, company, event, plant + An entity set is a set of entities of the same type that share the same properties. + Example: set of all persons, companies, trees, holidays + An entity is represented by a set of attributes; all members of an entity set. .e., descriptive properties possessed by Example: instructor cours (ID, name, street, city, salary ) (course_id, title, credits) + A subset of the attributes form a primary key of the entity set; ie., uniquely identifiying each member of the set. Entity Sets -- instructor and student instructor student Attributes-Attributes are the individual data elements that describe the properties of an entity. They provide detailed information about the entities represented in a databaseTees oF DescnPrion ATTRIBUTES “sinale vane 7 Only one vahae can be stored g + Example : Faculty id, DOB ; ~ More values are possible Multiple value |". Example : Faculty Phone_no, ‘The values which is derived from exating value * Example : AGE ‘the values keep on changing is not advisable to store in the database Derived vaiue | + Normally the values will be derived from existing value of another attribute, + AGE will be changing continuously. + it ean be derived from DOB { DOB never change) * NULL values are unknown undeclared Null value + An attribute does not have a value for a particular entity in an entity set E-R Notations EB] enityse > Atrbute Moy Non a Many One 8 eaivnstip BO telainship = Muted FE] wontnyss GAS) AN net One 1 cardinality Risieip tins Relationship Set Derived Attribute wl i Ling Toad ae ¥ — J Raattlp Ftaccposen RY [peice Sect @® seo citi tase inRlonip o tol Dai ering La etalon ED rimay key ED atte sit Wake E-R Diagram With Composite, Multivalued, and Derived Attributes (Ommiddle-initial > Gistnanie> name street-name — >>| Capartment- Cae Cast-name> i aD City — _— __Caaddress > —C state eustomer-id > — oo customer atin 7 Naipeode> Database Design And ER Diagrams-Entities, Attributes, and Entity SetsData Model Overview Data Description Constructs Data models provide high-level constructs to describe data, abstracting low-level storage details. Semantic Data Model Explore the concept of semantic data models and their role in representing data, Conceptual design Ahigh level data model provides the database designer with a conceptual frame work which includes ‘What kind of data required by the database users? How the database to be designed to fulfill the requirements? ¥ Database designer should choose the appropriate data model and translate these requirements into a conceptual schema. The schema developed at this conceptual-design phase provides a detailed overview of the enterprise. The designer review the schema to confirm that all data requirements. The designer can review the design to remove the redundant features Purpose of Data Storage ¥ Database Functionality: Explore the purpose of storing data in a database and the functions it serves within a DBMS. Effective Uses: Discuss examples of effective uses of a DBMS for storing and managing data. Internal Data Storage ¥ Relational DBMS: Understand how data is stored in tables within a relational DBMS. Data Structure: Explore the intemal data structures used for storage and rapid retrieval in a relational DBMS. Data StorageIndexing and Performance Indexing Data: Understand the role of indexing in optimizing data storage and retrieval performance. Y Query Optimization: Discuss how indexing contributes to query optimization within a DBMS. ¥ Data Compression ¥ Storage Efficiency: Explore the concept of data compression for optimizing storage space within a DBMS. Performance Impact: Discuss the impact of data compression on data retrieval and. processing performance. Distributed Data Storage ¥ Scalability: Explore the benefits of distributed data storage for scalability and fault tolerance. Data Replication: Discuss the role of data replication in distributed storage environments. Backup and Recovery Data Protection: Understand the importance of backup and recovery mechanisms for safeguarding stored data. Disaster Recovery: Discuss the strategies for recovering data in the event of system failures or data loss. Relationship Sets A relationship is an association among several entities Example: Hayes depositor A-102 customer entity relationship set_ account entity A relationship set is a mathematical relation among n > 2 entities, each taken from entity sets {(€1,€2, «eer € Bie € Ba, where (¢1, ¢, ....é,) isa relationshipExample: (Hayes, A-102) € depositor Relationship Set borrower 21-12-3123 19-28-3746 67-89-9011 55-55-5555, 24-66-8800 963-96-3963 i Princeton, 335-57-7991 Pittsfield customer loan Relationship Sets, An attribute can also be property of a relationship set. For instance, the depositor relationship set between entity sets customer and account may have the attribute access-date depositor(access-date) account(account-number) Jcustomer(customer-name) 24 May 1996 3 June 1996 Johnson 21 June 1996 Smith 10 June 1996 Hayes 17 June 1996 Tamer 28 May 1996 Jones 28 May 1996 Lindsay, 24 June 1996 23 May 1996 Degree of a Relationship Set Binary relationship setOne entity set involves in two entity sets is known as Binary relationship set. Example The faculty and student entity sets participate in relationship set counselor. ¥ In addition each student must have another faculty who works as department counselor ( Co-ordinator ) Then the faculty and student entity sets may participate in another relationship set, dept counselor. KEY CONSTRAINTS Keys are the entity set that is used to identify an entity within its entity set uniquely. + Not Null + Unique + Cheek + Primary Key + Foreign Key + Default NOT NULL KEY CONSTRAINT + The NOT NULL constraint in a database ensures that a column cannot contain a null value, + Used to enforce the requirement that a particular column must always have a value, which helps maintain data integrity and consistency. CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(S0) NOT NULL, Email VARCHAR(100) NOT NULL ); Unique Key Constraint -provides a unique/distinct values to specified columns. CREATE TABLE Employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(S0), last_name VARCHAR(50), emaill VARCHAR(100) UNIQUE, email2 VARCHAR(100) UNIQUE); Note:In the above example email and email2 cannot have same value. CHECK KEY CONSTRAINT -type of constraint in a relational database that ensures that the values in a column or a combination of columns satisfy a specific condition or set of conditions.-defined by the database designer and is enforced when data is inserted or updated in the table. CREATE TABLE Students (student_id INT PRIMARY KEY, first_name VARCHAR(S50), last_name VARCHAR(S0), age INT, grade CHAR(1), CONSTRAINT chk_age CHECK (age >= 18), CONSTRAINT chk_grade CHECK (grade IN (‘A’, 'B','C,,'D' 'F)) ); Note: chk_age: Ensures that the age of a student is 18 or greater. chk_grade: Ensures that the grade of a student is one of the specified values ('A', 'B’, 'C’, 'D', FY PRIMARY KEY CONSTRAINT + A PRIMARY KEY constraint is a column or a combination of columns that uniquely identifies each row (record) in a table. + Ttensures that each value in the primary key column(s) is unique and not null + A table can have only one PRIMARY KEY constraint + The primary key can consist of one or more columns, known as a composite primary key, which means multiple columns together uniquely identify each row. CREATE TABLE Employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(S0),last_name VARCHAR(S0),); Benefits of Primary Key Constraint: + Ensures data integrity = Prevents duplicate records in the table, ensuring data accuracy and consistency. + Unique identifier Allows for efficient referencing of individual records within and across tables, facilitating data retrieval and management. + Serves as a basis for relationships Used to establish relationships (foreign key constraints) between tables in a relational database. FOREIGN KEY CONSTRAINT + Establishes a relationship between two tables in a relational database. + Column or a combination of columns in one table that matches the primary key of another table. + Ensures that the values in the foreign key column(s) must exist in the corresponding primary key column(s) of the referenced table, or be NULL if the column is nullable.+ Helps maintain data consistency and integrity by preventing actions that would destroy the relationships between tables CREATE TABLE Table? ( column? datatype, foreign_key_column datatype, FOREIGN KEY (foreign_key_column) REFERENCES Table1(column!) ); Note: Foreign_key_column in Table? is a foreign key that references the primary key column column! in Tablel Actions on Foreign Key Constraints CASCADE m Ifa referenced row in the parent table is deleted or updated, the corresponding rows in the child table(s) are automatically deleted or updated as well. SET NULL m Ifa referenced row in the parent table is deleted or updated, the foreign key column(s) in the child table(s) are set to NULL. RESTRICT m= Prevents the deletion or update of a referenced row in the parent table if there are corresponding rows in the child table(s). NO ACTION Similar to RESTRICT, it prevents the deletion or update of a referenced row in the parent table. Benefits of Foreign Key Constraints Enforce referential integrity + Foreign key constraints ensure that relationships between tables are maintained and data remains consistent. Help in data modeling + Defines the structure of relationships between entities in a database, aiding in understanding and managing complex data structures, Assist in query optimization + Foreign key constraints provide information to the query optimizer, helping it make efficient execution plans for queries involving joins between related tables. Participation Constraints, Weak Entities 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. Partial Participation + Ifonly some entities in E participate in relationships in R, the participation of entity set E in relationship R is said to be partial. Example : ¥ In Figure : A, the participation of Project Entity Set in the relationship set is total while the participation of A in the relationship set is partial, ¥ In Figure : B, the participation of both Programmer Entity Set and Project Entity Set in the relationship set are total. Programmer Entity Set Project Entity Set Weak entities + Entities that cannot be uniquely identified by their attributes alone. + Relies on a related entity to provide additional attributes to make their identity unique. + Depends on strong entities for their existence and are typically associated with a one- to-many relationship, Points to be remembered on Weak entities Dependence on Strong Entities Weak entities do not have a primary key attribute that uniquely identifies them on their own. Instead, they depend on the existence of a related strong entity to which they are associated. Composite Key ‘A weak entity often has a composite key, which is a combination of its own partial key attributes along with the primary key attributes of the related strong entity. Existence Dependency Weak entities exist in the context of a specific strong entity.For example, consider the relationship between an ‘Order’ entity and ‘Order Items’. ‘Order Items’ may be a weak entity as it depends on the existence of a specific order to have meaning. Ownership Relationship Weak entities have a kind of ownership relationship with the strong entity they are associated with. This means they cannot exist without the presence of the related strong entity. Identifying Relationship The relationship between a weak entity and its associated strong entity is often referred to as an identifying relationship. It signifies that the weak entity's identity is identified within the context of the strong entity Database Modeling In database modeling, weak entities are often represented with a double rectangle to differentiate them from strong entities. Example Book Table: sev fe foun fr atabase Design 78-1234561 0 John Smith 2022 STMT SING) "Programming Basics" Jane Doc 2021 Book Copy Table: rors Lh a OT 778-1234500001 Good 178-78901 2100 New Note: In this scenario, each row in the ‘Book Copy' table is uniquely identified by the combination of its own 'Copy Number" and the 'ISBN' of the associated book from the 'Book' table. The ‘Book Copy’ entity is considered weak because it does not have a distinct identifier on its own and relies on the 'Book' entity for its identity. Aggregation,Case study: The internet shopAggregation One limitation of the E-R model is that it cannot express relationships among relationships. ¥ Toillustrate the need for such a construct, consider the ternary relationship project_guide, between an faculty, student and project Aggregation + Consider that , each faculty guiding a student on a project is required to file a monthly review report. + We model the review report as an entity review_report, with a primary key review_id, + One alternative for recording the ( student, project, faculty) combination to which a review corresponds is to create a quaternary (4-way) relationship set review_for between faculty , student, project, and review_report evaluation. + A quaternary relationship is required—a binary relationship between student and review report, for example, would not permit us to represent the (project, faculty) combination to which a review_ report corresponds. + Using the basic E-R modeling constructs, the following E-R diagram for the above constraints is obtained + This diagram with redundant relationships + The best way to model a situation such as the one just described is to use aggregation, + Aggregation is an abstraction through which relationships are treated as higher-level entities + Inthe given example, the relationship set project_ guide (relating the entity sets faulty, student, and project) as a higher-level entity set called project_guide. + Such an entity set is treated in the same manner as is any other entity set + We can then create a binary relationship review_for between project_guide and review report to represent which (student, project, faculty) combination an review_report is for. + Figure shows a notation for aggregation commonly used to represent this situation.Parry CONCEPTUAL DESIGN + Designer develop a high level description of the data in terms of the ER model. + Books and customers are modeled as entities and are related through orders that, customers place. + Orders is a relationship set connecting the Books and Customers entity sets. LOGICAL DATABASE DESIGN Create Books, Order and Customer table CREATE TABLE Books ( isbn CHAR(10), in stock INTEGER, price REAL, year published INTEGER, PRIMARY KEY (isbn)).. itle CHAR(80), author CHAR(80), qtyCREATE TABLE Orders ( isbn CHAR(10), cid INTEGER, qty INTEGER, order date DATE, ship date DATE, PRIMARY KEY (isbn,cid), FOREIGN KEY (isbn) REFERENCES Books, FOREIGN KEY (cid) REFERENCES Customers ) CREATE TABLE Customers ( cid INTEGER, cname CHAR(80), address CHAR(200), cardnum CHAR(16), PRIMARY KEY (cid) UNIQUE (cardnum)) SCHEMA REFINEMENT Developer analyzes the set of relations for possible redundancy. + The Books relation has only one key (isbn), and no other functional dependencies hold over the table. + The Customers relation has the key (cid), and since a credit card number uniquely identifies its card holder, the functional dependency cardnum —> cid also holds. Similarly cid is a key, cardnum is also a key. * Orders is not even in 3NF So the order relation can be splitted as follows: + Orders (ordernum, cid, order date) + Orderlists (ordernum, isbn, qty, ship date) ER Diagram Reflecting the Final Design te © PE VS ee Lee SS Introduction to Relational model Relational Model + The relational model is today the primary data model for commercial data processing applications. + Itattained its primary position because of its simplicity, which eases the job of the programmer / developer.+ Itis simple and easy to understand compared to earlier data models such as the network model or the hierarchical model. + The followings should be consider for Relational Model m= Structure of Relational Databases, m Database Schema = Keys m= Schema Diagrams m Relational Query Languages Structure of Relational Databases + A row ina table represents a relationship among a set of values. + A table is a collection of such relationships, + In mathematical terminology, a tuple is simply a sequence (or list) of values, + A relationship between n values is represented mathematically by an n-tuple of values, ie., a tuple with n values, which corresponds to a row in a table. + In relational model the term relation is used to refer to a table + The term tuple is used to refer to a row. + The term attribute refers to a column of a table. + For each attribute of a relation, there is a set of permitted values, called the + Domain of that attribute. + The domains of all attributes of relation be atomic. + The null value is a special value that signifies that the value is unknown or does not exist. Structure of Relational Databases + Each table will have a unique name (unique identification) + For example, consider the faculty table in the given figure, which stores information about faculty. + This table contains four attributes (columns) named faculty id, faculty_name, dept_name and salaryDatabase Schema + The database schema, which is the logical design of the database. + Database instance, which is a snapshot of the data in the database at a given instant in time. + The concept of a relation corresponds to the programming-language notion of a variable, + The concept of a relation schema corresponds to the programming-language notion of type definition. + Arelation schema consists of a list of attributes and their corresponding domains. Attribute Types Each attribute of a relation has a name The set of allowed values for each attribute is called the domain of the attribute Attribute values are (normally) required to be atomic, that is, indivisible + E.g. multivalued attribute values are not atomic + E.g. composite attribute values are not atomic ‘The special value null is a member of every domain The null value causes complications in the definition of many operations + we shall ignore the effect of null values in our main presentation and consider their effect laterSchema Diagram The given figure is for University Database A database schema, along with primary key and foreign key dependencies, can be depicted by schema diagrams. Each relation given as relation name and list of attributes Primary key attributes are underlined Foreign key dependencies appear as arrows from the foreign key attributes of the referencing relation to the primary key of the referenced relation. Referential integrity constraints other than foreign key constraints are not shown, explicitly in schema diagrams.