CST204 DBMS Module-1

CST 204 Database Management Systems

Textbook Authors Elmasri R. and S. Navathe

DIKW Pyramid • Data – Data are raw facts, simply exists but no significance. • Information – Data has given meaning with relationships. • Knowledge – Facts, information and skills acquired through experience. • Wisdom – Quality of having experience, knowledge and good judgement.

Database • A database is an organized collection of inter related data, generally stored and accessed electronically from a computer system.

Database Management System • The software that handles the storage, retrieval, and updating of data from the database

What DBMS Actually FACEBOOK Uses?

What DBMS Actually FACEBOOK Uses? • MySQL – Wall posts, user information, timeline details • Haystack – To store photos • Cassandra – Message inbox • Scribe – For login information • Varnish – Manage friends requests

Types of Data organizations • Structured data – Data whose elements are addressable for effective analysis. It has been organized into a formatted repository that is typically a database. • Semi-structured data – Information that does not reside in a database but that have some organizational properties that make it easier to analyze. With some process, you can store them in the relation database Eg. XML files • Unstructured data – Unstructured data is a data that is which is not organized in a predefined manner or does not have a pre-defined data model • Eg. Word, PDF

Semi structure example – XML files

FILE SYSTEMS VERSUS A DBMS • Permanent records are stored in various files • Data redundancy • Data inconsistency • Unshareable data • Unstandardized data • Insecure data

ADVANTAGES OF A DBMS • Controlling Redundancy • Controlling Inconsistency by avoiding redundancy • Data integrity and security • Facilitate sharing of data • Concurrent access and crash recovery • Providing Backup and Recovery • Potential for Enforcing Standards • Availability of Up-to-Date Information

WHERE DATABASE IS NOT NEEDED?!!! •Problem associated with centralization •Cost of software/hardware migration •Complexity of backup and recovery •Extra hardware may be required •System is likely to be complex

DATABASE SCHEMA / META DATA / CATALOGUE • Skeleton structure that represents the logical view of the entire database • The overall design of the database is called Database Schema

DATABASE INSTANCE • A snapshot of the database • The collection of information stored in the database at a particular moment is called an instance

THREE SCHEMA ARCHITECTURE IN A DBMS • Provide users with an abstract view of data • Hides certain details of how the data are stored and maintained • To hide the complexity from users who are not trained • Three levels of abstraction used • External Schema • Conceptual Schema • Physical Schema

THREE SCHEMA ARCHITECTURE IN A DBMS

THREE SCHEMA ARCHITECTURE IN A DBMS • EXTERNAL LEVEL • Data access to be customized (and authorized) at the level of individual users or groups of users. • It is the closest interface to the user. • With the help of GUI interface interact with the system without knowing which type of data stored and how the data stored in the database.

THREE SCHEMA ARCHITECTURE IN A DBMS • CONCEPTUAL LEVEL • The conceptual level or logical level describes the stored data in terms of the data model of the DBMS • It hides all physical data storage detail from the user and focuses on relations, data types, operations, and constraints. • Database programmer and Database administrator work at this level for creating functions, triggers, procedure, relations in the table. • In a relational DBMS, the conceptual level describes all relations that are stored in the database

THREE SCHEMA ARCHITECTURE IN A DBMS • PHYSICAL LEVEL • The physical level specifies additional storage details. • How the conceptual schema are actually stored on secondary storage devices such as disks and tapes. • It deals with data storage structure (B+ trees, Hash table) and data access way to access the data in the least time from the database. • The user does not interact with this level.

DATA INDEPENDENCE • As a DBMS expands, schema / structure needs to change over time to satisfy the requirements of the users. • The ability to modify a schema definition in one level without affecting the schema definition in the next higher level is called Data Independence. • The data independence can be of two types.

DATA INDEPENDENCE • Physical Data Independence: It refers to the ability to modify a schema followed at the physical level without affecting the schema definition followed at the logical level/conceptual level. • Logical Data Independence: It refers to the ability to modify a schema followed at the logical level/conceptual without affecting the schema definition followed at the view level.

PHYSICAL & LOGICAL DATA INDEPENDENCE

PEOPLE WHO DEAL WITH DATABASES • Database implementers/Designers who build DBMS software. • Naive users who interact with the system using predefined user interface and view reports etc. Eg. Bank employees • End users who wish to store and use data in a DBMS. • Application programmers develop packages that facilitate data access for end users, using the host or data languages and software tools that DBMS vendors provide. • Database Administrator • Schema definition • Storage structure and access method definition • Schema and physical organization modification • Granting of authorization for data access • Routine maintenance

The DBA skills • A good knowledge of physical database design • Excellent knowledge of Database backup and recovery scenarios • Good skills in all Database Management tools. • A good knowledge of Database security management. • A good knowledge of how DBMS acquires and manages resources. • Sound knowledge of the applications/activities at your company. • A DBA should have sound communication skills with management, development teams, vendors, systems administrators and other related service providers

DATABASE SYSTEM APPLICATIONS • Banking: for storing customer information, accounts, loans and banking transactions. • Airlines: For reservations and schedule information. • Universities: For student information, course registration and grade. • Credit card transactions: For purchase on credit card and generation of monthly statements. • Tele communications: For keeping records for calls made, generating monthly bills, maintaining balances on prepaid calling cards etc.

DATABASE SYSTEM APPLICATIONS • Finance: For storing information about holdings, sales and purchase of financial instruments such as stokes, bonds and for storing real time market data. • Sales: For customer, product, and purchase info. • Manufacturing: For management of the supply chain and for tracking production of items in factories and orders for items. • Human Resource: For information about employee, salaries, payroll taxes, benefits and for generation of pay checks.

DATABASE LANGUAGES • Data Definition Language (DDL) • Used to define the database structure or schema. • DDL includes the following commands: • CREATE - create table, to define an SQL relation. • ALTER – To add attributes to an existing relation. • DROP - To remove a relation from database. • TRUNCATE- To remove all records from a table, including all spaces allocated for the records are removed

DATABASE LANGUAGES • Data Manipulation Language (DML) • DML is used by database users to insert, delete, and update data in a database. • DML includes the following verbs: • SELECT – Retrieve data from the database • INSERT – Insert data in to a table • UPDATE – Updates existing data with in a table • DELETE – Deletes all records from a table, the space for the records remain.

DATABASE LANGUAGES •Data Control Language (DCL) •Example of DCL statements are: • GRANT – Give user‘s access privileges to data base. • REVOKE – Withdraw access privileges given with the GRANT command.

PARALLEL AND DISTRIBUTED DATABASES Some advantages • Parallel evaluation techniques and data distribution. • Performance: Using several resources (e.g., CPUs and disks) in parallel can significantly improve performance. • Increased availability: If a site containing a relation goes down, the relation continues to be available if a copy is maintained at another site. • Distributed access to data: We can find locality in the access patterns (e.g., A bank manager is likely to look up the accounts of customers at the local branch.

ARCHITECTURES FOR PARALLEL DATABASES • Shared-memory system, multiple CPUs are attached to an interconnection network and can access a common region of main memory. • Shared-disk system, each CPU has a private memory and direct access to all disks through an interconnection network. • Shared-nothing system, each CPU has local main memory and disk space, but no two CPUs can access the same storage area; all communication between CPUs is through a network connection.

ARCHITECTURES FOR PARALLEL DATABASES

Types of Distributed Databases • Homogeneous distributed database system: All sites have identical DBMS software, are aware of one another, exchanging information about transactions with other sites. • Heterogeneous distributed database system: Different sites may use different schemas and different DBMS software. The site may not be aware of one another.

Storing Data in a Distributed DBMS • Fragmentation Fragmentation consists of breaking a relation into smaller relations or fragments, and storing the fragments possibly at different sites. In horizontal fragmentation, each fragment consists of a subset of rows of the original relation. In vertical fragmentation, each fragment consists of a subset of columns of the original relation.

Storing Data in a Distributed DBMS • Replication Replication means we store several copies of a relation or relation fragment. An entire relation can be replicated at one or more sites.

Database System Architecture in Detail

DATA MODELS • Data models define how the logical structure of a database is modelled. • Data Models are fundamental entities to introduce abstraction in a DBMS. • Data models define how data is connected to each other and how they are processed and stored inside the system.

Entity-Relationship Model • Entity-Relationship (ER) Model is based on the notion of real-world entities and relationships among them. • ER Model creates entity set, relationship set, general attributes, and constraints. • ER Model is best used for the conceptual design of a database.

Entity-Relationship Model • ER Model is based on: • Entities and their attributes. • Relationships among entities.

An Entity • An entity can be a real-world object, that can be easily identifiable. • For example, in a school database, students, teachers, classes, and courses offered can be considered as entities. • All these entities have some attributes or properties that give them their identity. An Entity set • An entity set is a collection of similar types of entities. • A Students set may contain all the students of a school. • Entity sets need not be disjoint

ATTRIBUTES • Entities are represented by means of their properties called attributes. • All attributes have values. • For example, a student entity may have name, class, and age as attributes. • There exists a domain or range of values that can be assigned to attributes. • The set of permitted values for an attribute is called the Domain of that attribute. • For example, a student's name cannot be a numeric value. It has to be alphabetic. A student's age cannot be negative, etc

Types of Attributes • Simple attribute: Simple attributes are atomic values, which cannot be divided further. For example, a student's phone number is an atomic value of 10 digits. • Composite attribute: Composite attributes are made of more than one simple attribute. For example, a student's complete name may have first_name and last_name.

Types of Attributes • Derived attribute: Derived attributes are the attributes that do not exist in the physical database, but their values are derived from other attributes present in the database. • Example, age can be derived from data_of_birth. • Single-value attribute: Single-value attributes contain single value. For example: Social_Security_Number. • Multi-value attribute: Multi-value attributes may contain more than one value. For example, a person can have more than one phone number, email_address, etc.

ENTITY-SET AND KEYS • Super Key: A set of attributes (one or more) that collectively identifies an entity in an entity set. • Candidate Key: A minimal super key is called a candidate key. An entity set may have more than one candidate key. • Primary Key: A primary key is one of the candidate keys chosen by the database designer to uniquely identify the entity set.

ENTITY-SET AND KEYS • Alternate Key – Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternate or secondary keys. • Composite Key – A key that consists of more than one attribute to uniquely identify rows (also known as records & tuples) in a table is called composite key.

ENTITY-SET AND KEYS •Foreign key: • Foreign keys are the columns of a table that points to the primary key of another table. • They act as a cross-reference between tables.

RELATIONSHIP • The association among entities is called a relationship. • For example, an employee works_at a department, a student enrolls in a course. Here, Works_at and Enrolls are called relationships. • Relationship Set: A set of relationships of similar type is called a relationship set. • Degree of Relationship: The number of participating entities in a relationship defines the degree of the relationship. • Binary = degree 2 , Ternary = degree 3

MAPPING CARDINALITIES • Cardinality defines the number of entities in one entity set, which can be associated with the number of entities of other set via relationship set. • One-to-one • One-to-many • Many-to-one • Many-to-many

One-to-one • One-to-one: One entity from entity set A can be associated with at most one entity of entity set B and vice versa.

One-to-many • One entity from entity set A can be associated with more than one entities of entity set B, however an entity from entity set B can be associated with at most one entity.

Many-to-one • More than one entities from entity set A can be associated with at most one entity of entity set B, however an entity from entity set B can be associated with more than one entity from entity set A.

Many-to-many • One entity from A can be associated with more than one entity from B and vice versa.

E-R DIAGRAM REPRESENTATIONS ENTITY Entities are represented by means of rectangles. Rectangles are named with the entity set they represent.

E-R DIAGRAM REPRESENTATIONS ATTRIBUTES Attributes are the properties of entities. Attributes are represented by means of ellipses. Every ellipse represents one attribute and is directly connected to its entity. Fig: Simple Attributes

E-R DIAGRAM REPRESENTATIONS COMPOSITE ATTRIBUTES If the attributes are composite, they are further divided in a tree like structure. Every node is then connected to its attribute. That is, composite attributes are represented by ellipses that are connected with an ellipse.

E-R DIAGRAM REPRESENTATIONS MULTIVALUED ATTRIBUTES Multivalued attributes are depicted by double ellipse.

E-R DIAGRAM REPRESENTATIONS DERIVED ATTRIBUTES Derived attributes are depicted by dashed ellipse.

RELATIONSHIP • Relationships are represented by diamond-shaped box. • Name of the relationship is written inside the diamond-box. • All the entities (rectangles) participating in a relationship are connected to it by a line.

Binary Relationship and Cardinality • A relationship where two entities are participating is called a binary relationship. • Cardinality is the number of instance of an entity from a relation that can be associated with the relation.

Fig: One-to-one Fig: One-to-many

Fig: Many-to-one Fig: Many-to-many

A simple ER model for a College

PARTICIPATION CONSTRAINTS • Total Participation: Each entity is involved in the relationship. Total participation is represented by double lines. • Partial participation: Not all entities are involved in the relationship. Partial participation is represented by single lines.

WEAK ENTITIES •For a weak entity set to be meaningful, it must be associated with another entity set, called the identifying or owner entity set. • The weak entity set is said to be ―existence dependent on the identifying entity set. •The relationship associating the weak entity set with the identifying entity set is called the ―identifying relationship.

WEAK ENTITIES & IDENTIFYING RELATIONSHIPS

WEAK ENTITIES & IDENTIFYING RELATIONSHIPS • A weak entity set does not have a primary key. • The discriminator of a weak entity set is also called the partial key of the entity set. • The primary key of a weak entity set is formed by the primary of the identifying entity set, plus the weak entity set‘s discriminator. • In the case of the entity set payment, its primary key is {loan _number, payment_number} where loan_number is the primary key of the identifying entity set, namely loan, and payment_number distinguishes payment entities within the same loan.

GENERALIZATION & SPECIALIZATION •The ER Model has the power of expressing database entities in a conceptual hierarchical manner. •As the hierarchy goes up, it generalizes the view of entities, and as we go deep in the hierarchy, it gives us the detail of every entity included. •This representation model is known as Enhanced Entity Relationship Model (EER).

GENERALIZATION • As mentioned above, the process of generalizing entities, where the generalized entities contain the properties of all the generalized entities is called generalization. • In generalization, a number of entities are brought together into one generalized entity based on their similar characteristics.

SPECIALIZATION • In specialization, a group of entities is divided into sub-groups based on their characteristics. In a school database, persons can be specialized as teacher, student, or a staff, based on what role they play in school as entities

INHERITANCE • The attributes of a Person class such as name, age, and gender can be inherited by lower-level entities such as Student or Teacher.

Relationships of Degree 3 ( Ternary Relationships ) •A ternary relationship exists when exactly three entity type participates. •When such a relationship is present we say that the degree is 3.

Design an Entity Relationship (ER) model for a College Database

Design an Entity Relationship (ER) model for a College Database • A college contains many departments • Each department can offer any number of courses • Many instructors can work in a department • 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 take any number of courses • A course can be taken by only one instructor • A student can enroll for any number of courses • Each course can have any number of students

Steps to draw an ER diagram •Step 1 : Identify the Entities •What are the entities here? •From the statements given, the entities are •Department •Course •Instructor •Student

Steps to draw an ER diagram • Step 2 : Identify the relationships • One department offers many courses. But one particular course can be offered by only one department. hence the cardinality between department and course is One to Many (1:N) • One department has multiple instructors . But instructor belongs to only one department. Hence the cardinality between department and instructor is One to Many (1:N)

Steps to draw an ER diagram • One department has only one head and one head can be the head of only one department. Hence the cardinality is one to one. (1:1) • One course can be enrolled by many students and one student can enroll for many courses. Hence the cardinality between course and student is Many to Many (M:N) • One course is taught by only one instructor. But one instructor teaches many courses. Hence the cardinality between course and instructor is Many to One (N :1)

Steps to draw an ER diagram •Step 3: Identify the key attributes •"Department_Name" can identify a department uniquely. Hence Department_Name is the key attribute for the Entity "Department". •Course_ID is the key attribute for "Course" Entity. •Student_ID is the key attribute for "Student" Entity. •Instructor_ID is the key attribute for "Instructor" Entity.

Steps to draw an ER diagram •Step 4: Identify other relevant attributes •For the department entity, other attributes are location •For course entity, other attributes are course_name,duration •For instructor entity, other attributes are first_name, last_name, phone •For student entity, first_name, last_name, phone

Design an Entity Relationship (ER) model for a Vehicle Dealership System

Design an Entity Relationship (ER) model for a Banking System Person Opens an Account Person using ATM for Transaction Entities User, Account, ATM

CST204 DBMS Module-1

More Related Content

Similar to CST204 DBMS Module-1

Recently uploaded

CST204 DBMS Module-1