Download DATABASE MANAGMENT SYSTEM and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
1 – Database System Architecture
Define the following terms.
Data
Fact that can be recorded or stored. E.g. Person Name, Age, Gender and Weight etc.
Information
When data is processed, organized, structured or presented in a given context so as to make it useful, it is called information.
Database
A Database is a collection of inter-related (logically-related) data. E.g. Books Database in Library, Student Database in University etc.
DBMS (Database Management System)
A database management system is a collection of inter-related data and set of programs to manipulate those data. DBMS = Database + Set of programs E.g. MS SQL Server, Oracle, My SQL, SQLite, MongoDB etc.
Metadata
Metadata is data about data. Data such as table name, column name, data type, authorized user and user access privileges for any table is called metadata for that table.
Data dictionary
Data dictionary is an information repository which contains metadata. It is usually a part of the system catalog.
Data warehouse
Data warehouse is an information repository which stored data. It is design to facilitate reporting and analysis.
Field
A field is a character or group of characters that have a specific meaning. It is also called a data item. It is represented in the database by a value. For Example customer id, name, society and city are all fields for customer Data.
Record
A record is a collection of logically related fields. For examples, collection of fields (id, name, address & city) forms a record for customer.
Explain disadvantages of file system (file processing systems)
compare to Database management system. OR
Explain disadvantages of conventional file-based system
compared to Database management system.
Data Redundancy
It is possible that the same information may be duplicated in different files. This leads to data redundancy.
1 – Database System Architecture
Data redundancy results in memory wastage. For example, consider that some customers have both kinds of accounts - saving and current. In this case, data about customers such as name, address, e-mail and contact number will be duplicated in both files, saving accounts file and current account file. In other words, same information will be stored in two different locations (files). And, it wastes memory.
Data Inconsistency
Due to data redundancy, it is possible that data may not be in consistent state. For example, consider that an address of some customer changes. And, that customer has both kinds of accounts. Now, it is possible that this changed address is updated in only one file, leaving address in other file as it is. As a result of this, same customer will have two different addresses in two different files, making data inconsistent.
Difficulty in Accessing Data
Accessing data is not convenient and efficient in file processing system. For example, suppose, there is a program to find information about all customers. But, what if there is a need to find out all customers from some particular city. In this case, there are two choices here: One, find out all customers using available program, and then extract the needed customers manually. Second, develop new program to get required information. Both options are not satisfactory. For each and every different kind of data access, separate programs are required. This is neither convenient nor efficient.
Limited Data Sharing
Data are scattered in various files. Different files may have different formats. And these files may be stored in different folders (directories) may be of different computers of different departments. So, due to this data isolation, it is difficult to share data among different applications.
Integrity Problems
Data integrity means that the data contained in the database is both correct and consistent. For this purpose, the data stored in database must satisfy certain types of constraints (rules). For example, a balance for any account must not be less than zero. Such constraints are enforced in the system by adding appropriate code in application programs. But, when new constraints are added, such as balance should not be less than Rs. 5000, application programs need to be changed. But, it is not an easy task to change programs whenever required.
Atomicity Problems
Any operation on database must be atomic. This means, operation completes either 100% or 0%. For example, a fund transfer from one account to another must happen in its entirely. But, computer systems are vulnerable to failure, such as system crash, virus attack. If a system failure occurs during the execution of fund transfer operation, it may possible that
1 – Database System Architecture
Data Integrity
Data in database must be correct and consistent. So, data stored in database must satisfy certain types of constraints (rules). DBMS provides different ways to implement such type of constraints (rules). This improves data integrity in a database.
Data Security
Database should be accessible to user in a limited way. DBMS provides way to control the access to data for different user according to their requirement. It prevents unauthorized access to data. Thus, security can be improved.
Concurrent Access
Multiple users are allowed to access data simultaneously. Concurrent access to centralized data can be allowed under some supervision. This results in better performance of system and faster response.
Guaranteed Atomicity
Any operation on database must be atomic. This means, operation must be executed either 100% or 0%. This type of atomicity is guaranteed in DBMS.
List and explain the applications of DBMS.
Airlines and railways
Airlines and railways use online databases for reservation, and for displaying the schedule information.
Banking
Banks use databases for customer inquiry, accounts, loans, and other transactions.
Education
Schools and colleges use databases for course registration, result, and other information.
Telecommunications
Telecommunication departments use databases to store information about the communication network, telephone numbers, record of calls, for generating monthly bills, etc.
Credit card transactions
Databases are used for keeping track of purchases on credit cards in order to generate monthly statements.
E-commerce
Integration of heterogeneous information sources (for example, catalogs) for business activity such as online shopping, booking of holiday package, consulting a doctor, etc.
Health care information systems and electronic patient record
Databases are used for maintaining the patient health care details in hospitals.
1 – Database System Architecture
Digital libraries and digital publishing
Databases are used for management and delivery of large bodies of textual and multimedia data.
Finance
Databases are used for storing information such as sales, purchases of stocks and bonds or data useful for online trading.
Sales
Databases are used to store product, customer and transaction details.
Human resources
Organizations use databases for storing information about their employees, salaries, benefits, taxes, and for generating salary checks.
Describe functions (responsibility, roles, and duties) of DBA to
handle DBMS.
DBA
The full name of DBA is Database Administrator. Database Administrator is a person in the organization who controls the design and the use of database.
Functions or Responsibilities of DBA are as under:
Schema Definition
DBA defines the logical schema of the database. A schema refers to the overall logical structure of the database. According to this schema, database will be designed to store required data for an organization.
Storage Structure and Access Method Definition
DBA decides how the data is to be represented in the database. Based on this, storage structure of the database and access methods of data is defined.
Defining Security and Integrity Constraints
DBA decides various security and integrity constraints. DDL (Data Definition Language) provides facilities to specifying such constraints.
Granting of Authorization for Data Access
The DBA determines which user needs access to which part of the database. According to this, various types of authorizations (permissions) are granted to different users. This is required to prevent unauthorized access of a database.
Liaison with Users
DBA is responsible to provide necessary data to user. User should be able to write the external schema, using DDL (Data Definition Language).
Assisting Application Programmers
DBA provides assistance to application programmers to develop application programs.
1 – Database System Architecture
It describes what data are stored in the database and what relationships exist among those data. It is also known as a logical level. Conceptual view is defined by conceptual schema. It describes all records and relationship.
External Level
This is the highest level of data abstraction. It is also known as view level. It describes only part of the entire database that a particular end user requires. External view is describes by external schema. External schema consists of definition of logical records, relationship in the external view and method of deriving the objects from the conceptual view. This object includes entities, attributes and relationship.
Explain Mapping. OR
Explain external and internal mapping. OR
What is mapping? Describe type of mapping.
Mapping
The process of transforming requests and results between the three levels is called mapping.
Types of Mapping
Conceptual/Internal Mapping External/Conceptual Mapping
Conceptual/Internal Mapping
It relates conceptual schema with internal schema. It defines correspondence between the conceptual schema and the database stored in physical devices. It specifies how conceptual records and fields are presented at the internal level. If the structure of stored database is changed, then conceptual/internal mapping must be changed accordingly and conceptual schema can remain invariant. There could be one mapping between conceptual and internal levels.
External/Conceptual Mapping
It relates each external schema with conceptual schema. It defines correspondence between a particular external view and conceptual schema. If the structure of conceptual schema is changed, then external/conceptual mapping must be changed accordingly and external schema can remain invariant. There could be several mappings between external and conceptual levels.
1 – Database System Architecture
Explain Data Independence.
Data Independence
Data independency is the ability to modify a schema definition in one level without affecting a schema definition in the next higher level.
Types of data independence
Physical data independence Logical data independence
Physical data independence
Physical data independence allows changing in physical storage devices or organization of file without change in the conceptual view or external view. Modifications at the internal level are occasionally necessary to improve performance. Physical data independence separates conceptual level from the internal level. It is easy to achieve physical data independence.
Logical data independence
Logical data independence is the ability to modify the conceptual schema without requiring any change in application programs. Conceptual schema can be changed without affecting the existing external schema. Modifications at the logical level are necessary whenever the logical structure of the database is altered. Logical data independence separates external level from the conceptual view. It is difficult to achieve logical data independence.
Explain Data Abstraction in DBMS.
Database systems are made-up of complex data structures. To ease the user interaction with database, the developers hide internal irrelevant details from users. This process of hiding irrelevant details from user is called data abstraction.
Explain different database users.
There are four different database users.
Application programmers
These users are computer professionals who write application programs using some tools. E.g. Software developers
Sophisticated users
These users interact with system without writing program. They form their request in a database query language. E.g. Analyst.
Specialized users
These users write specialized database applications that do not fit into the traditional data processing framework. E.g. Database Administrator.
Naive users
These users are unsophisticated users who have very less knowledge of database system.
1 – Database System Architecture
Storage Manager Units:
Storage manager units provide interface between the low level data stored in database and the application programs & queries submitted to the system. Authorization Manager — Checks the authority of users to access data. Integrity Manager — Checks for the satisfaction of the integrity constraints. Transaction Manager — Preserves atomicity and controls concurrency. File Manager — Manages allocation of space on disk storage. Buffer Manager — Fetches data from disk storage to memory for being used.
In addition to these functional units, several data structures are required to implement physical storage system. These are described below: Data Files — To store user data. Data Dictionary and System Catalog — To store metadata. It is used heavily, almost for each and every data manipulation operation. So, it should be accessed efficiently. Indices — To provide faster access to data items. Statistical Data — To store statistical information about the data in the database. This information is used by the query processor to select efficient ways to execute a query.
1 – Database System Architecture
Database System Architecture
naive users (tellers, agents, web-users)
application programmers
sophisticated users (analyst)
database administrator
use write use use
application interfaces
application program
query tool
administra- tion tool interfaces
compiler and linker DML queries^
DDL interpreter
application program object code
DML compiler and organizer
query evaluation engine query processor
buffer manager file manager authorization and integrity manager
transaction manager
data dictionary
statistical data
indices
data
storage manager
disk storage
2 – Data Models
Define the following terms.
Entity
An entity is a thing or object or person in the real world that is distinguishable from all other object. E.g. book, student, employee, college etc…
Entity sets
An entity set is a set of entities of same type that share the same properties or attributes. E.g. the set of all students in a college can be defined as entity set student.
Relationship
Relationship is an association (connection) between several entities. Relationship between 2 entities is called binary relationship. E.g. book is issued by student where book and student are entities and issue is relation.
Relationship set
Relationship set is a set of relationships of the same type.
Attributes
Attributes are properties hold by each member of an entity set. E.g. entity is student and attributes of student are enrollmentno, name, address, cpi etc
Types of attributes
Simple attribute: It cannot be divided into subparts. E.g. cpi, rollno Composite attribute : It can be divided into subparts. E.g. name (first-name, middle- name, last-name), address. Single valued attribute: It has single data value. E.g. enrollmentno, birthdate Multi valued attribute : It has multiple data value. E.g. phoneno (may have multiple phones). Stored attribute: It’s value is stored manually in database. E.g. birthdate Derived attribute : It’s value is derived or calculated from other attributes. E.g. age (can be calculated using current date and birthdate).
Descriptive attributes
A relationship may also have attributes like an entity. These attributes are called descriptive attributes. E.g. Student gets degree certificate on 14th^ March 2011. Student and Degree are entities, gets certificate is relation and certificate date is an attribute of relationship.
2 – Data Models
Recursive relationship set
The same entity set participates in a relationship set more than once then it is called recursive relationship set. E.g. an employee entity participated in relationship under with department entity as an employee as well manager also.
Degree of relationship
The degree of a relationship is the number of entity types that participate in the relationship. The three most common relationships in ER models are Unary, Binary and Ternary. A unary relationship is when both participant entities in the relationship are the same entity. Example: Subjects may be prerequisites for other subjects.
A binary relationship is the relationship between two different entities. The University might need to record which teachers taught which subjects.
A ternary relationship is the relationship between three different entities. The University might need to record which teachers taught which subjects in which courses.
teacher (^) teaches subject
subject
is prerequisite for
student (^) issue degree certificate
student-id
student-name
Branch certi-no^ name
Issue date
2 – Data Models
A customer is connected with only one loan using the relationship borrower and a loan is connected with only one customer using borrower.
One-to-many relationship
An entity in A is associated with any number (zero or more) of entities in B and an entity in B is associated with at most (only) one entity in A.
In the one-to-many relationship a loan is connected with only one customer using borrower and a customer is connected with more than one loans using borrower.
customer (^) borrower loan
customer-id
customer-name
customer-city
customer-address
loan-no amount
A 1
A 2
A 3
A 4
B 1
B 2
B 3
B 4
A B
2 – Data Models
Many-to-one relationship
An entity in A is associated with at most (only) one entity in B and an entity in B is associated with any number (zero or more) of entities in A.
In a many-to-one relationship a loan is connected with more than one customer using borrower and a customer is connected with only one loan using borrower.
Many-to-many relationship
A 1
A 2
A 3
A 4
B 1
B 2
B 3
B 4
A B
customer (^) borrower loan
customer-id
customer-name
customer-city
customer-address
loan-no amount
A 1
A 2
A 3
A 4
B 1
B 2
B 3
B 4
A B
2 – Data Models
Explain weak entity set with the help of example.
Weak entity set
An entity set that does not have a primary key is called weak entity set. The existence of a weak entity set depends on the existence of a strong entity set. Weak entity set is indicated by double rectangle. Weak entity relationship set is indicated by double diamond. The discriminator (partial key) of a weak entity set is the set of attributes that distinguishes between all the entities of a weak entity s et. The primary key of a weak entity set is created by combining the primary key of the strong entity set on which the weak entity set is existence dependent and the weak entity set’s discriminator. We underline the discriminator attribute of a weak entity set with a dashed line. E.g. in below fig. there are two entities loan and payment in which loan is strong entity set and payment is weak entity set. Payment entity has payment-no which is discriminator. Loan entity has loan-no as primary key. So primary key for payment is (loan-no, payment-no).
loan
loan-no amount^ payment-no^ loan-amount
payment-date
loan-payment payment
Strong Entity Weak Entity Relationship Weak Entity
customer (^) borrower loan
customer-id
customer-name
customer-city
customer-address
loan-no amount
Partial Participation Total Participation
2 – Data Models
Explain the Superclass and Subclass in E-R diagram with the
help of example.
Superclass
A superclass is an entity from which another entity can be derived. A superclass is a generic entity set which has a relationship with one or more subclasses. For example, an entity set account has two subsets saving_account and current_account. So an account is superclass. Each member of subclass is also a member of superclass. So any saving account or a current account is a member of entity set account.
Subclass
A subclass is an entity that is derived from another entity. A class is a subset of entities in an entity set which has attributes distinct from those in other subset. For example, entities of the entity set account are grouped in to two classes saving_account and current_account. So saving_account and current_account are subclasses.
Explain the difference between Specialization and
Generalization in E-R diagram.
Specialization Generalization
It will work in Top-down approach. It will work in Bottom-up approach The process of creating sub-groupings within an entity set is called specialization.
The process of creating groupings from various entity sets is called generalization. Specialization is a process of taking a sub set of higher level entity set to form a lower-level entity set.
Generalization is a process of taking the union of two or more lower-level entity sets to produce a higher-level entity set. Specialization starts from a single entity set; it creates different low-level entity set using some different features.
Generalization starts from the number of entity sets and creates high-level entity set using some common features.
account
saving account current account
superclass
sublass
