Lecture notes Database management system

lecture notes on database management system and database management systems exam questions and answers. database management system objective type questions and answers
Dr.TomHunt Profile Pic
Dr.TomHunt,United States,Teacher
Published Date:23-07-2017
Your Website URL(Optional)
DMC 1654 DATABASE MANAGEMENT SYSTEMS NOTES UNIT -1 DATABASE MANAGEMENT SYSTEMS 1.1 INTRODUCTION 1.1.1 Features of a database 1.1.2 File systems vs Database systems 1.1.3 Drawbacks of using file systems to store data 1.2 OVERALL SYSTEM STRUCTURE 1.2.2 Levels of Abstraction 1.2.3 Instances and Schemas 1.3 DATA MODELS 1.3.1 The Network Model 1.3.2 The Hierarchical Model 1.4 ENTITY- RELATIONSHIP MODEL 1.4.1 Entity Sets 1.4.2 Attributes 1.4.3 Keys 1.4.4 E-R Diagram Components 1.4.5 Weak Entity Set 1.4.6 Specialization 1.4.7 Generalization 1.4.8 Aggregation 1 Anna University Chennai www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS NOTES UNIT -1 DATABASE MANAGEMENT SYSTEMS 1.1 INTRODUCTION A database is a collection of data elements (facts) stored in a computer in a systematic way, such that a computer program can consult it to answer questions. The answers to those questions become information that can be used to make decisions that may not be made with the data elements alone. The computer program used to manage and query a database is known as a database management system (DBMS). So a database is a collection of related data that we can use for  Defining - specifying types of data  Constructing - storing & populating  Manipulating - querying, updating, reporting A Database Management System (DBMS) is a software package to facilitate the creation and maintenance of a computerized database. A Database System (DBS) is a DBMS together with the data itself. 1.1.1 Features of a database:  It is a persistent (stored) collection of related data.  The data is input (stored) only once.  The data is organised (in some fashion).  The data is accessible and can be queried (effectively and efficiently). 1.1.2 File systems vs Database systems: DBMS are expensive to create in terms of software, hardware, and time invested. So why use them? Why couldn’t we just keep all our data in files, and use word- processors to edit the files appropriately to insert, delete, or update data? And we could write our own programs to query the data This solution is called maintaining data in flat files. So what is bad about flat files? Anna University Chennai 2 www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS  Uncontrolled redundancy NOTES  Inconsistent data  Inflexibility  Limited data sharing  Poor enforcement of standards  Low programmer productivity  Excessive program maintenance  Excessive data maintenance 1.1.3 Drawbacks of using file systems to store data:  Data redundancy and inconsistency Due to availability of multiple file formats, storage in files may cause duplication of information in different files.  Difficulty in accessing data In order to retrieve, access and use stored data, need to write a new program to carry out each new task.  Data isolation To isolate data we need to store them in multiple files and different formats.  Integrity problems Integrity constraints (E.g. account balance 0) become part of program code which has to be written every time. It is hard to add new constraints or to change existing ones.  Atomicity of updates Failures of files may leave database in an inconsistent state with partial updates carried out. E.g. transfer of funds from one account to another should either complete or not happen at all  Concurrent access by multiple users Concurrent access of files is needed for better performance and it also true that uncontrolled concurrent accesses of files can lead to inconsistencies. E.g. two people reading a balance and updating it at the same time Several Security related problems might be caused in file system. 3 Anna University Chennai www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS 1.2 OVERALL SYSTEM STRUCTURE NOTES Figure: 1.1 – System Architecture The Overall structure of the database system is shown in Figure 1.1. The Central component is known as the core DBMS which has a query evaluation engine to execute the queries. The disk storage is used to store the data. 1.2 Database Users:  Users are differentiated by the way they expect to interact with the system  Application programmers – interact with the system through DML calls Anna University Chennai 4 www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS  Sophisticated users – form requests in a database query language NOTES  Specialized users – write specialized database applications that do not fit into the traditional data processing framework  Naive users – invoke one of the permanent application programs that have been written previously 1.2.2 Levels of Abstraction Physical level : Figure 1.2 shows the three level architecture for database systems. describes how a record (E.g., customer) is stored. Logical level: describes data stored in database, and the relationships among the data. Type customer = record name : string; street : string; city : integer; end; View level: application programs hide details of data types. Views can also hide information (E.g., salary) for security purposes. View of Data Figure 1.2: Architecture for a database system 1.2.3 Instances and Schemas Similar to types and variables in programming languages which we already know, Schema is the logical structure of the database E.g., 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 5 Anna University Chennai www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS  Logical schema: database design at the logical level NOTES Instance is the actual content of the database at a particular point of 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. 1.3 DATA MODELS o A structure that demonstrates all the required features of the parts of the real world, which is of interest to the users of the information in the model. o Representation and reflection of the real world (Universe of Discourse). o A set of concepts that can be used to describe the structure of a database: the data types, relationships, constraints, semantics and operational behaviour. o It is a tool for data abstraction o A collection of tools for describing  data  data relationships  data semantics  data constraints Some of the data models are : o Entity-Relationship model o Relational model o Other models:  object-oriented model  semi-structured data models  Older models: network model and hierarchical model A data model is described by the schema, which is held in the data dictionary.  Student(studno,name,address)  Course(courseno,lecturer)  Schema  Student(123,Bloggs,Woolton)  Instance  (321,Jones,Owens) Anna University Chennai 6 www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS Example: Consider the database of a bank and its accounts, given in Table 1.1 NOTES and Table 1.2 Table 1.1. “Account” contains details of Table 1.2. “Customer” contains details of the customer of a bank the bank account Account Customer Account Balance Name Area City Number Let us define the network and hierarchical models using these databases. 1.3.1 The Network Model  Data are represented by collections of records.  Relationships among data are represented by links.  Organization is that of an arbitrary graph and represented by Network diagram.  Figure 1.3 shows a sample network database that is the equivalent of the relational database of Tables 1.1 and 1.2. Figure.1.3. A Sample Network Database  The CODASYL/DBTG database was derived on this model. Constraints in the Network Model: 1. Insertion Constraints: Specifies what should happen when a record is inserted. 2. Retention Constraints: Specifies whether a record must exist on its own or always be related to an owner as a member of some set instance. 3. Set Ordering Constraints: Specifies how a record is ordered inside the database. 4. Set Selection Constraints: Specifies how a record can be selected from the database. 1.3.2 The Hierarchical Model  Similar to the network model and the concepts are derived from the earlier systems Information Management System and System-200. 7 Anna University Chennai www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS  Organization of the records is as a collection of trees, rather than arbitrary NOTES graphs.  In the hierarchical model, a Schema represented by a Hierarchical Diagram as shown in Figure 1.4 in which o One record type, called Root, does not participate as a child record type. o Every record type except the root participates as a child record type in exactly one type. o Leaf is a record that does not participate in any record types. o A record can act as a Parent for any number of records. Figure.1.4. A Sample Hierarchical Database The relational model does not use pointers or links, but relates records by the values they contain. This allows a formal mathematical foundation to be defined. 1.4 ENTITY- RELATIONSHIP MODEL Figure 1.5 shows a sample E.R. diagram which consists of entity sets and relationship sets. Figure 1.5: Sample E-R Diagram Anna University Chennai 8 www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS 1.4.1 Entity Sets: Collection of entities such as customer and account NOTES A database can be modeled as: – a collection of entities, – relationships among entities (such as depositor) An entity is an object that exists and is distinguishable from all 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 1.4.2 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-name,social-security,customer-street,customer-city) account= ( account-number,balance) Domain – the set of permitted values for each attribute Attribute types: –Simple and composite attributes. –Single-valued and multi-valued attributes. –Null attributes. –Derived attributes. –Existence Dependencies 1.4.3 Keys:  A super key ofan entity set is a set of one or more attributes whose values uniquely determine each entity.  A candidate key of an entity set is a minimal super key. – social-security is candidate key of customer – account-number is candidate key of account  Although several candidate keys may exist, one of the candidate keys is selected to be the primary key.  The combination of primary keys of the participating entity sets forms a candidate key of a relationship set. 9 Anna University Chennai www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS – must consider the mapping cardinality and the semantics of the NOTES relationship set when selecting the primary key. – (social-security, account-number) is the primary key of depositor 1.4.4 E-R Diagram Components  Rectangles represent entity sets.  Ellipses represent attributes.  Diamonds represent relationship sets.  Lines link attributes to entity sets and entity sets to relationship sets.  Double ellipses represent multivalued attributes.  Dashed ellipses denote derived attributes.  Primary key attributes are underlined. 1.4.5 Weak Entity Set An entity set that does not have a primary key is referred to as a weak entity set. The existence of a weak entity set depends on the existence of a strong entity set; it must relate to the strong set via a one-to-many relationship set. The discriminator (or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set. The primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent, plus the weak entity set’s discriminator. A weak entity set is depicted by double rectangles. 1.4.6 Specialization This is a Top-down design process as whown in Figure 1.6 in which; we designate subgroupings within an entity set that are distinctive from other entitie in the set. These subgroupings become lower-level entity sets that have attributes or participate in relationships that do not apply to the higher-level entity set. Depicted by a triangle component labeled ISA (i.e., savings-account “is an” account) Anna University Chennai 10 www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS NOTES Figure 1.6: Specialization 1.4.7 Generalization:  A bottom-up design process – combine a number of entity sets that share the same features into a higher-level entity set.  Specialization and generalization are simple inversions of each other; they are represented in an E-R diagram in the same way.  Attribute Inheritance – a lower-level entity set inherits all the attributes and relationship participation of the higher-level entity set to which it is linked. Design Constraints on Generalization: Constraint on which entities can be members of a given lower-level entity set. – condition-defined – user-defined - Constraint on whether or not entities may belong to more than one lower-level entity set within a single generalization. – disjoint – overlapping - Completeness constraint – specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within a generalization. – total - partial 11 Anna University Chennai www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS 1.4.8 Aggregation: NOTES Figure 1.7 shows the need for aggregation since it has two relationship sets. The second relationship set is necessary because loan customers may be advised by a loan-officer. Figure: 1.7. AGGREGATION Relationship sets borrower and loan-officer represent the same information - Eliminate this redundancy via aggregation as shown in Figure 1.8 Points in Aggregation – Treat relationship as an abstract entity. – Allows relationships between relationships. – Abstraction of relationship into new entity. – Without introducing redundancy, the following diagram represents that: – A customer takes out a loan – An employee may be a loan officer for a customer-loan pair Anna University Chennai 12 www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS AGGREGATION EXAMPLE: NOTES Figure 1.8 An Example of Aggregation Short Questions: 1. State the properties of a Database. 2. State the approaches of Database system. 3. What are the levels of Abstraction in database? 4. When to prefer to use Database and when not to ? 5. What do you mean by instance & schema? 6. State the properties of a Network Model. 7. State the constraints in Network Model. 8. Draw the Network Diagram for any given database. 9. Draw the Hierarchical Diagram for any given database. 10. What do you mean by Root Record and Leaf Record? 11. Define Entity, Attribute, Relationship, Entity Type, Entity Instance and Entity Class. 12. Differentiate Weak and Strong Entity Set. 13. What are the various notations for ER Diagrams? 13 Anna University Chennai www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS 14. What is participation constraint? Give its types. NOTES 15. Define Generalization and Specialization. Descriptive Questions: 1. Compare the File System Model and DBMS. 2. Explain a Database Systems Approach. 3. Compare the Hierarchical Model and Network Model. 4. Design a database using Hierarchical Model. 5. Design a database using Network Model. 6. Explain the various types of Attributes with suitable examples. 7. State the different types of Participation Constraint and explain with a diagrammatic example. SUMMARY  Database systems provide features for effective storage and manipulation of data in comparison with file systems  Data models are used for logical representation of data  E.R. diagrams help to design relational database schemas and permit us to convert them into tables Anna University Chennai 14 www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS NOTES UNIT -2 RELATIONAL MODEL 2.1 INTRODUCTION 2.1.1 Data Models 2.1.2 Relational Database: Definitions 2.1.3 Why Study the Relational Model? 2.1.4 About Relational Model 2.1.6 Design approaches Informal Measures for Design 2.2 Relational Design 2.2.1 Simplest approach (not always best) 2.2.2 Relational Model Basic Structure Relational Data Model Attribute Types Relation Schema RELATION INSTANCE Relations are Unordered 2.3 RELATIONAL ALGEBRA 2.3.1 Select Operation Picks rows 2.3.2 Project Operation - Picks columns 2.3.3 Union Operation – Example 2.3.4 Set Difference Operation – Example 2.3.5 Cartesian-Product Operation-Example 2.3.6 Composition of Operations 2.3.7 Rename Operation 2.3.8 Banking Example Example Queries in Relational Algebra 2.3.9 Formal Definition of Relational Algebra Additional Operations Aggregate Functions and Operations 15 Anna University Chennai www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS Outer Join NOTES Null Values Modification of the Database 2.4 STRUCTURED QUERY LANGUAGE (SQL) 2.4.1 Introduction 2.4.2. Create Table 2.4.3 ALTER Statement 2.4.4 DROP Statements 2.4.5 SELECT Statement 2.4.6 INSERT INTO Statement 2.4.7 UPDATE Statement 2.4.8 DELETE FROM Statement 2.4.9 Basic Queries Using Single Row Functions 2.4.10 Complex Queries Using Group Functions 2.5 VIEWS 2.5.1 Creation of a view 2.5.2 Dropping a View 2.5.3 Disadvantages of Views 2.5.4 Updates Through View 2.5.5 Views Defined Using Other Views 2.5.6 View Expansion 2.6 INTEGRITY CONSTRAINTS 2.6.1 Referential Integrity 2.6.2 Referential Integrity in the E-R Model 2.6.3 Checking Referential Integrity on Database Modification 2.7 RELATIONAL ALGEBRA AND CALCULUS 2.7.1 Tuple Relational Calculus 2.7.2 Domain Relational Calculus 2.8 RELATIONAL DATABASE DESIGN 2.8.1 Functional Dependencies 2.8.2 Use of Functional Dependencies 2.8.3 Closure of a Set of Functional Dependencies Anna University Chennai 16 www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS 2.9 NORMALIZATION – NORMAL FORMS NOTES 2.9.1 Uses of Normalization 2.9.2 Pitfalls in Relational Database Design 2.9.3 Redundancy 2.9.4 Decomposition 2.9.5 Normalization Using Functional Dependencies 2.10 TYPES OF NORMAL FORMS 2.10.1 First Normal Form (1NF) 2.10.2 Second Normal Form (2NF) 2.10.3 Third Normal Form (3NF) 2.10.4 Boyce Codd Normal Form (BCNF) 2.10.5 Third Normal Form: (Detailed Disassion) 2.10.6 Design Goals of 4NF 2.11 FURTHER NORMAL FORMS 2.11.1 Overall Database Design Process 2.11.2 ER Model and Normalization 2.11.3 Universal Relation Approach 2.11.4 Denormalization for Performance 2.11.5 Other Design Issues 17 Anna University Chennai www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS NOTES Anna University Chennai 18 www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS NOTES UNIT - 2 RELATIONAL MODEL 2.1 INTRODUCTION 2.1.1 Data Models  A data model is a collection of concepts for describing data.  A schema is a description of a particular collection of data, using a given data model.  The relational model of data is the most widely used model today. o Main concept: relation, basically a table with rows and columns. o Every relation has a schema, which describes the columns, or fields (that is, the data’s structure). 2.1.2 Relational Database: Definitions  Relational database: a set of relations  Relation: made up of 2 parts: o Instance : a table, with rows and columns. o Schema : specifies name of relation, plus name and type of each column.  E.g. Students(sid: string, name: string, login: string, age: integer, gpa: real).  Can think of a relation as a set of rows or tuples that share the same struc- ture. 2.1.3 Why Study the Relational Model?  Most widely used model.  Vendors: IBM, Informix, Microsoft, Oracle, Sybase,etc.  “Legacy systems” in older models were complex  E.g., IBM’s IMS 19 Anna University Chennai www.Annauniversityplus.comDMC 1654 DATABASE MANAGEMENT SYSTEMS  Recent competitor: XML NOTES  A synthesis emerging: XML & Relational Example of a Relation Table 2.1 shown a sample relation Table 2.1 Accounts relation 2.1.4 About Relational Model  Order of tuples not important but Order of attributes not important (in theory)  Collection of relation schemas with intension of Relational database schema  Corresponding relation instances as extension of Relational database  intension vs. extension simulates schema vs. data  metadata includes schema 2.1.5 Good Schema At the logical level… o Easy to understand o Helpful for formulating correct queries At the physical storage level… o Tuples are stored efficiently o Tuples are accessed efficiently 2.1.6 Design approaches Top-down o Start with groupings of attributes achieved from the concep- tual design and mapping o Design by analysis is applied Bottom-up o Consider relationships between attributes o Build up relations o Also called design by synthesis Anna University Chennai 20 www.Annauniversityplus.com

Advise: Why You Wasting Money in Costly SEO Tools, Use World's Best Free SEO Tool Ubersuggest.