DBMS CHAPTER 1
Data: Data are the raw facts that can be obtained after some experiments or observations. Raw data is of no use until and unless we process it to find some useful information form it.
Database: A database is the collection of related persistent data and contains information relevant to an enterprise. The database is also called the repository or container for a collection of data files. For example, university database for maintaining information about students, courses and grades in university
Database Management System: Management of data involves a way to store data and also provides a mechanism for manipulation of that data. Database management systems are basically designed to manage large volume of information. A database system is basically just a computerized record-keeping system. A database system involves four major components: data, hardware, software, and users. The database management system (DBMS) is the software that handles all access to the database. It is defined as the collection of interrelated data and a set of programs to access those data. The primary goal of DBMS is to store and retrieve data in both convenient (easy method) and efficient (capable of performing well) manner. Some of the examples of DBMS are Oracle, SQL-Server, MySQL, MS Access etc.
Databse design
Database
design is the organization of data according to a database model. The designer
determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model. Database management system manages the data accordingly.
Why Is Database
Design so Important?
A good database design is important in ensuring consistent data,
elimination of data redundancy,
efficient execution of queries and high performance application. Taking the
time to design a database saves
time and frustration during development, and a well-designed database ensures ease of access and
retrieval of information. Database design is the structure a database uses to plan, store and
manage data. Data consistency is achieved when a database is designed
to store only useful and required data.
Traditional file system
File System helps to store
a collection of raw files of data into a hard disk, while
DBMS is a software system,
and it helps to store, manipulate or recover data.
File system is a method of
organising the files with a hard disk or other medium of storage. File system
arranges the files and helps in
retrieving the files, when required. It is compatible with different file
types, such as mp3, doc, txt, mp4,etc
and these are also grouped
into directories. It also
influences the method
of writing and reading
data to the hard disk.
Applications of
DBMS: ·
· Banking: To store information about customers, their account number, balance etc.
· Airlines: For reservations and schedule information. ·
Telecommunication: To keep records of customers, call made, balance left, generating monthly bills etc.
· Universities: To keep records of students, courses, marks of students etc.
· Sales: To keep information of customers, products list, purchase information etc.
· Manufacturing: To store orders, tracking production of items etc. · Human Resources: To keep records of employee, their salary, bonus etc.
Characteristics of database approach:
The main characteristics of database approach are discussed below:
1. Self describing nature of database system: The fundamental characteristic of the database approach is that the database system contains not only the database itself but also complete definition or description of the database structure.
2. Support of multiple view of the data: Let us consider an example of student information system of a college, where all the data of student, courses, information of college etc are stored in a database. It is Downloaded from: http://www.bsccsit.com/ Chapter 1 Prepared by: Er. Bipul Kr. Yadav obvious that there is more than one user of this system and also their interest is different. i.e. student are generally interested in finding out the marks obtained, whereas teachers are able to put marks, view information about students, similarly, visitors are able to find the information of the college.
3. Sharing of data and multi user transaction processing: Database system should allow multiple users to access same database at the same time. For example, in online air ticket reservation system many users are accessing the same site at the same time. Hence for every seat, DBMS should ensure that only one user should be given access to reserve the seat.
Advantages and disadvantages of DBMS Advantages of DBMS
Data independence: DBMS provides abstract view of data. Application programs are independent from details of data representation and storage.
Efficient data access: DBMS provides verity of sophisticated techniques to store and retrieve data efficiently.
Data integrity and security: DBMS allow to enforce integrity constraints on data. For example before inserting salary information for an employee, DBMS can enforce integrity constraint to check salary is not exceeded department budget. DBMS can also enforce access controls, what data is visible to what class of users.
Data administration: DBMS provides centralized administration of data. It is appropriate when several no. of database user shares data. It improves the overall performance of database system.
Disadvantage of DBMS
• Complex architecture of DBMS software
• DBMS software cost
• Since DBMS is optimized certain kind of workloads (e.g. answering complex queries or handling many concurrent requests) its performance may not appropriate for certain specialized applications.
• Abstract view of data presented by DBMS may not match for certain applications. For example, relational databases does not supports flexible analysis of text data
• If specialized performance or data manipulation requirements are central to an application, DBMS is not appropriate for such application. The added benefits of a DBMS (e.g. flexible querying, security, concurrent access and crash recovery) may not require for applications.
Chapter 4:
Concept of Entity
Relationship(ER) Model
Why use ER Diagrams?
Here, are reasons for using the ER Diagram
·
Helps you to define terms related to entity
relationship modeling
·
Provide
a preview of how all your tables should
connect, what fields
are going to be on
each table
·
Helps to describe
entities, attributes, relationships
·
ER diagrams are translatable into relational tables
which allows you to build databases quickly
·
ER diagrams can be used by database
designers as a blueprint for implementing data in
specific software applications
·
The database designer
gains a better understanding of the information to be contained in the database with the help of ERP diagram
·
ERD Diagram allows you to communicate with the logical structure of the database
to users
E= Entity and R=Relationship
ER diagram is the relation between entities
Following are the main components and its symbols in ER Diagrams:
·
Rectangles: This Entity
Relationship Diagram symbol represents entity
types
·
Ellipses : Symbol represent
attributes
·
Diamonds: This symbol represents relationship types
·
Lines: It links attributes to entity types and entity
types with other
relationship types
·
Primary key: attributes are underlined
·
Double Ellipses: Represent multi-valued attributes
·
Primary key: attributes are underlined
·
Double Ellipses: Represent multi-valued attributes
ER Diagram Symbols
Components of the ER Diagram
2) Attributes and
1) ENTITY
A real-world thing either living or non-living that is easily recognizable and nonrecognizable. It is anything in the enterprise that is to be represented in our database. It may be a physical thing or simply a fact about the enterprise or an event that happens in the real world.
An entity can be place, person, object, event or a concept, which stores data in the database. The characteristics of entities are must have an attribute, and a unique key. Every entity is made up of some ‘attributes’ which represent that entity.
Examples of entities:
·
Person: Employee, Student, Patient
·
Place: Store, Building
·
Object: Machine, product, and Car
·
Event: Sale, Registration, Renewal
·
Concept: Account, Course
Weak Entities
A weak entity is a type of entity which doesn’t have its key attribute. It can be identified uniquely by considering the primary key of another entity. For that, weak entity sets need to have participation.
In above ER Diagram examples, “Trans No” is a discriminator within a group of transactions in an ATM.
Attributes
It is a single-valued property of either an entity-type or a relationship-type. For example, a lecture might have attributes: time, date, duration, place, etc. An attribute in ER Diagram examples, is represented by an Ellipse
1. Key Attribute –
The attribute which uniquely identifies each entity in the entity set is called key attribute.For example, Roll_No will be unique for each student. In ER diagram, key attribute is represented by an oval with underlying lines.
2. Composite Attribute –
An attribute composed of many other attribute is called as composite attribute. For example, Address attribute of student Entity type consists of Street, City, State, and Country. In ER diagram, composite attribute is represented by an oval comprising of ovals.
3. Multivalued Attribute –
An attribute consisting more than one value for a given entity. For example, Phone_No (can be more than one for a given student). In ER diagram, multivalued attribute is represented by double oval.
4. Derived Attribute –
An attribute which can be derived from other attributes of the entity type is known as derived attribute. e.g.; Age (can be derived from DOB). In ER diagram, derived attribute is represented by dashed oval.
The complete entity type Student with its attributes can be represented as:
Relationship Type and Relationship Set:
A relationship type represents the association between entity types. For example,‘Enrolled in’ is a relationship type that exists between entity type Student and Course. In ER diagram, relationship type is represented by a diamond and connecting the entities with lines.
A set of relationships of same type is known as relationship set. The following relationship set depicts S1 is enrolled in C2, S2 is enrolled in C1 and S3 is enrolled in C3.
Degree of a relationship set:
The number of different
entity sets participating in a relationship set is called as degree
of a relationship set.
1. Unary Relationship –
When there is only ONE entity set participating in a relation, the relationship is called as unary relationship. For example, one person is married to only one person.
2. Binary Relationship –
When there are TWO entities set participating in a relation, the relationship is called as binary relationship.For example, Student is enrolled in Course.
3. n-ary Relationship –
When there are n entities set participating in a relation, the relationship is called as n- ary relationship.
TYPES OF RELATIONSHIP
1.
One to one – When each entity in each entity set can take part only once in the relationship, the cardinality is one to one. Let us assume that a male can marry to
one female and a female can marry to one male. So the relationship will be one to one.
Using Sets, it can be represented as:
2.
Many to one OR One to many – When entities
in one entity set can take part only once in the relationship set and entities in other entity set can take part more than once in the relationship set, cardinality is many to one. Let us assume that a student can take only one course but one course can be taken by many students. So the cardinality will be n to 1. It means that for one course there can be n students but for one student, there will be only one course.
Using Sets, it can be represented as:
In this case, each student is taking only 1 course but 1 course has been taken by many students.
3. Many to many – When
entities in all entity sets can take part more than once in the relationship cardinality is many to many. Let us assume that a student
can take more than one course and one course can be taken by many students.
So the relationship will be many to many.
Using sets, it can be represented as:
In this example, student S1 is enrolled in C1 and C3 and Course C3 is enrolled by S1, S3 and S4. So it is many to many relationships.
Participation Constraint:
Participation Constraint is applied on the entity participating in the relationship set.
1.
Total Participation – Each entity in the entity set must participate in the relationship. If each student must enroll in a course, the participation of student will be total. Total participation is shown by double line in ER diagram.
2.
Partial Participation – The entity in the entity set may or may NOT participate in the relationship. If some courses are not enrolled by any of the student,
the participation of course will be partial.
The diagram depicts the ‘Enrolled in’ relationship set with Student Entity set having total participation and Course Entity set having partial participation.
Using set, it can be represented as,
Every student in Student Entity set is participating in relationship but there exists a course C4 which is not taking part in the relationship.
Weak Entity Type and Identifying Relationship:
As discussed
before, an entity type has a key attribute which uniquely identifies
each entity in the entity set. But there exists some entity type for which key attribute
can’t be defined. These are called Weak Entity type.
For example, A company may store the information of dependents (Parents, Children, Spouse) of an Employee. But the dependents don’t have existence without the employee. So Dependent will be weak entity type and Employee will be Identifying Entity type for Dependent.
A weak entity type is represented by a double rectangle. The participation of weak entity type is always total. The relationship between weak entity type and its identifying strong entity type is called identifying relationship and it is represented by double diamond
*******Some examples
of ER diagram are given below******
1)
ER diagram of Student and book
2. ER diagram
of Author and Book
3.
ER diagram of Customer
and loan
