DBMS-Unit-1 ppt.pptx

Database Management Systems  Database Management System (DBMS) ⚫ Data ⚫ Information ⚫ Metadata ⚫ Database ⚫ Collection of interrelated data ⚫ Set of programs to access the data ⚫ Goal is to store and retrieve data, where data is organized in the form of tables, views, sch...

Database Management Systems  Database Management System (DBMS) ⚫ Data ⚫ Information ⚫ Metadata ⚫ Database ⚫ Collection of interrelated data ⚫ Set of programs to access the data ⚫ Goal is to store and retrieve data, where data is organized in the form of tables, views, schema, reports etc. ⚫ Database Applications: ⚫ Banking: transactions ⚫ Airlines: reservations, schedules ⚫ Universities: registration, grades ⚫ Sales: customers, products, purchases ⚫ Railway reservations ⚫ Telecommunications ⚫ Human resources: employee records, salaries, tax deductions ⚫ Databases can be very large.  University Database Example ⚫Application program examples ⚫Add new students, instructors, and courses ⚫Register students for courses, and generate class rosters ⚫Assign grades to students, compute grade point averages (GPA) and generate transcripts ⚫In the early days, database applications were built directly on top of file systems  History of Database Systems ⚫1950s and early 1960s: ⚫First DBMS named Integrated Data Source(IBS) was designed by Charles Bachman ⚫Data processing using magnetic tapes for storage ⚫ Tapes provided only sequential access ⚫Punched cards for input ⚫Late 1960s and 1970s: ⚫Ted Codd introduced IBMs Information management Systems(IMS) ⚫Hard disks allowed direct access to data ⚫High-performance (for the era) transaction processing A Sample Relational Database History (cont.) ⚫1980s: ⚫Relational model became popular and accepted as main Database paradigm. ⚫ SQL becomes industrial standard ⚫Object-oriented database systems ⚫1990s: ⚫New data models and powerful query languages like Oracle, SQL Server, IBM DB2, Informix, etc ⚫2000s: ⚫XML and XQuery standards ⚫Automated database administration Drawbacks of using file systems to store data ⚫Data redundancy and inconsistency ⚫ Multiple file formats, duplication of information in different files ⚫Difficulty in accessing data ⚫ Need to write a new program to carry out each new task ⚫Data isolation — multiple files and formats ⚫Integrity problems ⚫ Integrity constraints (e.g., account balance > 0) become “buried” in program code rather than being stated explicitly ⚫ Hard to add new constraints or change existing ones  Drawbacks of using file systems to store data (Cont.) ⚫ Atomicity of updates ⚫ Failures may leave database in an inconsistent state with partial updates carried out ⚫ Example: Transfer of funds from one account to another should either complete or not happen at all ⚫ Concurrent access by multiple users ⚫ Concurrent access needed for performance ⚫ Uncontrolled concurrent accesses can lead to inconsistencies ⚫ Example: Two people reading a balance (say 100) and updating it by withdrawing money (say 50 each) at the same time ⚫ Security problems ⚫ Hard to provide user access to some, but not all, data Database systems offer solutions to all the above problems File System Database Management System (DBMS) 1. It is a software system that manages 1. It is a software system used for creating and managing the and controls the data files in a computer databases. DBMS provides a systematic way to access, update, and system. delete data. 2. File system does not support multi-user 2. Database Management System supports multi-user access. access. 3. Data consistency is less in the file 3. Data consistency is more due to the use of normalization. system. 4. File system is not secured. 4. Database Management System is highly secured. 5. File system is used for storing the 5. Database management system is used for storing the structured unstructured data. data. 6. In the file system, data redundancy is 6. In DBMS, Data redundancy is low. high. 7. No data backup and recovery process is 7. There is a backup recovery for data in DBMS. present in a file system. 8. Cost of a file system is less than the 9. Cost of database management system is more than the file system. DBMS. 9. If one application fails, it does not 10. If the database fails, it affects all application which depends on it. affect other application in a system. 10. In the file system, data cannot be 11. In DBMS, data can be shared as it is stored at one place in a shared because it is distributed in database. different files. 11. These system does not provide 12. This system provides concurrency facility. concurrency facility. 12. Example: NTFS (New technology file system), 13. Example: Oracle, MySQL, MS SQL Server, DB2, Microsoft EXT (Extended file system), etc. Access, etc.  Data Models ⚫ A collection of tools for describing ⚫ Data ⚫ Data relationships ⚫ Data semantics ⚫ Data constraints ⚫ Relational model ⚫ Entity-Relationship data model (mainly for database design) ⚫ Object-Oriented data models (Object-oriented and Object- relational) ⚫ Other older models: ⚫ Network model ⚫ Hierarchical model Relational Model ⚫ Relational model Colum ⚫ Example of tabular data in the relational ns model Row s Entity-Relationship Model ∙ Entity-Relationship Model or simply ER Model is a high-level data model diagram. ∙ In this model, we represent the real-world problem in the pictorial form to make it easy for the stakeholders to understand. ∙ It is also very easy for the developers to understand the system by just looking at the ER diagram. ∙ We use the ER diagram as a visual tool to represent an ER Model. The Entity-Relationship Model ⚫ Models an enterprise as a collection of entities and relationships ⚫ Entity: a “thing” or “object” in the enterprise that is distinguishable from other objects ⚫ Described by a set of attributes ⚫ Relationship: an association among several entities ⚫ Represented diagrammatically by an entity- relationship diagram: Object-Oriented Data Model ∙ The real-world problems are more closely represented through the object-oriented data model. ∙ In this model, both the data and relationship are present in a single structure known as an object. ∙ We can store audio, video, images, etc in the database which was not possible in the relational model. ∙ In this model, two are more objects are connected through links. We use this link to relate one object to other objects. ∙ This can be understood by the example given below. Hierarchical Model ∙ Hierarchical Model was the first DBMS model. ∙ This model organizes the data in the hierarchical tree structure. ∙ The hierarchy starts from the root which has root data and then it expands in the form of a tree adding child node to the parent node. ∙ This model easily represents some of the real-world relationships like food recipes, sitemap of a website etc. ∙ Example: We can represent the relationship between the shoes present on a shopping website in the following way: Network Model ∙ This model is an extension of the hierarchical model. ∙ It was the most popular model before the relational model. ∙ This model is the same as the hierarchical model; the only difference is that a record can have more than one parent. ∙ It replaces the hierarchical tree with a graph. ∙ Example: In the example below we can see that node student has two parents i.e. CSE Department and Library. This was earlier not possible in the hierarchical model. View of Data An architecture for a database system  Levels of Abstraction ⚫ Physical level: describes how a record (e.g., customer) is stored. ⚫ Logical level: describes what data is stored in database, and the relationships among the data. type instructor = record ID : string; name : string; dept_name : string; salary : integer; end; ⚫ View level: application programs hide details of data types. Views can also hide information (such as an employee’s salary) for security purposes.  Instances and Schemas ⚫ Similar to types and variables in programming languages ⚫ Schema – the logical structure of the database ⚫ Example: The database consists of information about a set of customers and accounts and the relationship between them ⚫ Analogous to type information of a variable in a program ⚫ Physical schema: database design at the physical level ⚫ Logical schema: database design at the logical level ⚫ Instance – the actual content of the database at a particular point in time ⚫ Analogous to the value of a variable ⚫ Physical Data Independence – the ability to modify the physical schema without changing the logical schema ⚫ Applications depend on the logical schema ⚫ In general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others. Structure of DBMS Database Users and Administrators Databas e Database Users ⚫ Users are differentiated by the way they expect to interact with the system ⚫ Application programmers – interact with system through DML calls. ⚫ Sophisticated users – form requests in a database query language. ⚫ Specialized users – write specialized database applications that do not fit into the traditional data processing framework. ⚫ Naïve users – invoke one of the permanent application programs that have been written previously Database Administrator ⚫ Coordinates all the activities of the database system. ⚫ Has a good understanding of the enterprise’s information resources and needs. Duties of DBA ⚫ Installing and configuration of db. 1. Deciding data recovery and backup methods 2. Managing data integrity 3. Database tuning. 4. Capacity issues 5. Database design 6. Monitoring performance and responding to changes 7. Granting users authority to access the database. 8. Provides help and support to user.  Query Processing 1. Parsing and translation 2. Optimization 3. Evaluation  Storage Management ⚫ Storage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system. ⚫ The storage manager is responsible to the following tasks: ⚫Interaction with the file manager ⚫Efficient storing, retrieving and updating of data ⚫ Issues: ⚫Storage access ⚫File organization ⚫Indexing and hashing  Transaction Management ⚫ What if the system fails? ⚫ What if more than one user is concurrently updating the same data? ⚫ A transaction is a collection of operations that performs a single logical function in a database application ⚫ Transaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures. ⚫ Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database.  SQL ⚫ SQL stands for Structured Query Language or sequel ⚫ SQL is declarative language ⚫ It is a command based language ⚫ It is not case sensitive. ⚫ Every command should end with ‘;’ ⚫ Every command starts with “verb” ⚫ SQL is used to access & manipulate data in databases ⚫ It is similar to English. This language is developed in the year 1972 by “IBM”. ⚫ Top SQL DBs are MS SQL Server, Oracle, DB2, and MySQL Database Languages 1. DDL (Data Definition Language) 2. DML (Data Manipulation Language) 3. DRL/DQL (Data Retrieval/Query Language) 4. TCL (Transaction Control Language) 5. DCL (Data Control Language) DDL(Data Definition Language) This language is used to manage database objects such as table and view. 1) CREATE - creates a new table or a view of a table in database 2) ALTER – modifies contents of a table. (COLUMN) 3) DROP - deletes an entire table or a view of a table 4) TRUNCATE – delete all rows(values) from table 5) RENAME – renames the table name DML(Data Manipulation Language) This language is used to manipulate the data. 1) INSERT – inserts data into table 2) UPDATE – update/modifies data in the table(ROWS) 3) DELETE - deletes data from table DRL/DQL(Data Retrieval/Query Language) This language is used to retrieve the data from the table SELECT – retrieve the data from the table TCL(Transaction Control Language) This language is used to maintain the transaction of database 1. COMMIT 2. ROLLBACK 3. SAVEPOINT DCL(Data Control Language) This language is used to control the access of the data to the users 1. GRANT - assign privilege 2. REVOKE - remove privilege Use database; Create command: This command is used to create a table. SYNTAX: CREATE TABLE (Column1 DataType, Column1 DataType,…); EX: create table student(snum integer,sname varchar(20)); Insert Command: SYNTAX: INSERT INTO () VALUES EX:Insert into student values(501,’ramu’); Select Command: SYNTAX: SELECT FROM WHERE EX: select * from student; Note: where we use * to indicate all the fields information (ALL the columns and the rows are displayed). PRACTICE Create emp, salgrade and dept tables Select: This command is used to return the data from the table. SELECT * FROM ; select * from emp; // * represent ALL Note: where we use * to indicate all the fields information (ALL the columns and the rows are displayed) Selecting specific columns: select empno, ename, deptno from emp;  Database Design The process of designing the general structure of the database: ▪ Requirement Analysis ▪ Conceptual database design ▪ Logical database design ▪ Schema refinement ▪ Physical database design ▪ Security design E- R DATA MODELING 🞆 An entity is an object that exists and is distinguishable from other objects. ⚫ Examples: ⚫ Person: PROFESSOR, STUDENT ⚫ Place: STORE, UNIVERSITY ⚫ Object: MACHINE, BUILDING ⚫ Event: SALE, REGISTRATION 🞆 An entity is represented with a RECTANGLE employ ee 🞆 Entities have attributes ⚫ Example: people have names and addresses Domain – the set of permitted values for each attribute 🞆 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 ATTRIBUTES 🞆 An entity is represented by a set of attributes, that is descriptive properties ⚫ possessed by all members of an entity set.  Example: ⚫ customer = (Customer_id, name, street, city, salary ) ⚫ movie= (title, director, written by, duration, release date) 🞆 Attributes are represented with ELLIPSE Customer nam stre _id e et 🞆 Use LINES to link attributes to entities Draw entity and attributes for a PILOT ⚫Sol 1: Pid nam sala ry e licen PIL se OT ⚫Draw entity and attributes for a FAN Types of attributes in ERD  ⚫ Simple attribute- Is one which cannot be divided further. ⚫ Composite attribute -Composite attributes are those attributes which are composed of other simple attributes  ⚫ Single valued attributes - Are those attributes that can take only one value for a given entity.. Multi valued attribute - Multi valued attributes are those attributes which can take more then one value for a given entity.  ⚫ Derived attribute - Are those attributes that can be derived from other attributes. ⚫ Key attribute - Are those which can uniquely identify an entity in entity set Use CAR as an entity and draw all possible attributes that includes all attribute types EXAMPLE  Types of Entity type Strong Entity Type Weak Entity Type. Strong Entity Type: Strong entity are those entity types which has a key attribute. The primary key helps in identifying each entity uniquely. It is represented by a rectangle. In the below example, Roll_no identifies each element of the table uniquely and hence, we can say that STUDENT is a strong entity type ⚫ Weak Entity Type: Weak entity type doesn't have a key attribute. Weak entity type can't be identified on its own. It depends upon some other strong entity for its distinct identity.. ⚫ Every weak entity should have total participation ⚫ Entity set maynot have sufficient attributes to form a primary key ⚫ Two weak entity sets is not possible to relate each other. ⚫ Entity se with no proper candidate key Relationships and Relationship sets. Relationship ⚫ The association among two or more entities is called a relationship. For example, an employee works_in department. ⚫ A relationship is represented by diamond shape in ER diagram, it shows the relationship among entities. Relationship Set ⚫ A relationship set is a collection of similar relationships. Like entities, a relationship too can have attributes. These attributes are called descriptive attributes. NOTE: Schema – specifies relation name,attribute,column or field name and domain name(values) Instance – it is the collection of information stored in database at particular moment  Degree of Relationship ⚫ It is the total number of entities that participate in a relationship. ⚫ Degree of a relation is no. of fields/attributes. ⚫ Cardinality of a relation is no. tuples Unary relationship – relationship between instances of same entity class - It is also called as Recursive Relationship. Unary Relationship =degree 1 Binary relationship - relationship between instances of 1 entity class with instances of another entity class Binary relationship = degree 2  Ternary relationship - relationship between instances of 3 entity class with one another. Ternary relationship = degree 3 N-ary relationship - relationship between instances of n entity class with one another ⚫ n-ary Relationship = degree n Mapping Cardinality It is the no. of instances of one entity class that can be associated with each instance of another entity class. ⚫ One-to-one relationship (1:1): One instance in an entity (parent) refers to one and only one instance in the related entity (child). ⚫ One-to-many relationship (1:M): One instance in an entity (parent) refers to one or more instances in the related entity (child) ⚫ Many-to-one relationship (M:1): many instance in an entity (parent) refers to one instance in the related entity(child) One to one One to many Many-to-many relationship (M:N): exists when one instance of the first entity (child) entity (parent) can relate to many instances of the second entity (child), and one instance of the second entity can relate to many instances of the first entity. Many to many  We express cardinality constraints by drawing either a directed line (→), signifying “one,” or an undirected line (—), signifying “many,” between the relationship set and the entity set. Or, by numbering each entity. * or, m for many.  Participation of an Entity Set in a Relationship Set ⚫ Total participation: all entities in the entity set participates in atleast one relationship in the relationship set E.g. participation of loan in borrower is total Every loan must have a customer associated to it. ⚫ Partial participation: some entities may not participate in any relationship in the relationship set E.g. participation of employee in depatment is partial  EXERCISE ⚫ Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted. I  EXERCISE ⚫ Construct an E-R diagram for a car-insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents.  EXERCISE ⚫ Construct an E-R diagram for the registrar's office. Document all assumptions you make about the mapping constraints. Assumptions: A class meets only at one particular place and time. This diagram does not attempt to model a class meeting at different places or at different times ⚫ There is no guarantee that the database does not have two classes meeting at the same place and time ⚫ Each class has a unique instructor ⚫ (b) Construct appropriate tables for the ER Diagram  EXERCISE ⚫ Design an E-R diagram for keeping track of the exploits of your favorite sports team. You should store the matches played, the scores in each match, the players in each match and individual player statistics for each match. Summary statistics should be modeled as derived attributes. ⚫ SOLUTION  EXERCISE ⚫ Suppose you are given the following requirements for a simple database for the National Hockey League (NHL): ⚫ the NHL has many teams, ⚫ each team has a name, a city, a coach, a captain, and a set of players, ⚫ each player belongs to only one team, ⚫ each player has a name, a position (such as left wing or goalie), a skill level, and a set of injury records, ⚫ a team captain is also a player, ⚫ a game is played between two teams (referred to as host_team and guest_team) and has a date (such as May 11th, 1999) and a score (such as 4 to 2). ⚫ Construct a clean and concise ER diagram for the NHL database Draw an ER diagram for university database consisting of four entities ⚫ i. Student ⚫ ii. Department ⚫ iii. Class ⚫ iv. Faculty and convert into tables ⚫ A student has a unique id and can enroll for multiple classes and has at most one major ⚫ Faculty must belong to department and faculty can take multiple classes ⚫ Every student will get a grade for the class for he/she was enrolled Specialization it Top-down approach Process of approaching the subsets of an entity set(superclass) that share some distinguishing characteristics. Here, superclass is defined first and then subclass is defined next followed by superclass specific attributes and relationship sets are added Generalization it bottom-up approach identifying some common characteristics of collection of entity sets and creating a new entity set that contains entities with common characteristics. Here, subclass is defined first and then superclass is defined next followed by relationship that involves the superclass are then defined Aggregation it is a process when relation between two entities is treated as single entity. Relationship set participate in another relationship set. We can use either aggregation or ternary relationship for 3 or more entity sets. Denoted by dashed box. Aggregation v/s ternary relationship Using ternary relationship instead of aggregation Keys Single attribute or group of attributes used to recognize individual entity or group of entities. 1. Primary key – single attribute used to recognize single entity - unique identification of each row in a table - identified by underlined - unique values & no NULL values 2. Composite key – group of attribute used to recognize single entity - allows duplicate values - collective values must be unique for every instance - multiple values can be used to create a primary key 3. Alternative key – single attribute other than primary key is recognize single entity - candidate key - set of fields that uniquely identify a table 4. Secondary key - single/group of attribute used to recognize group of entities - primary key/composite key can’t be used as secondary key - any part of composite key can be used as secondary key - other than primary key Keys 5. Sort key – single/group of attribute used to recognize group of entities - sorted in ascending order - only secondary keys are used 6. Index key – it is an attribute used to access a single entity efficiently - no. of matches are reduced 7. Super key – set of tuples uniquely identifies a relation - candidate key is a subset of super key - every candidate key is a super key but every super key maynot be candidate key 8. Unique key - same as primary key but allows NULL value. 9. Foreign key - it is a set of attributes references primary key or alternative key of the same table or other table. ex: create table enrolled(sid integer,cid varchar(20),grade varchar(10), PRIMARY KEY(sid,cid), FOREIGN KEY(sid) references student(sid));