Class 9 Computer Science Database Management System Notes

Database Management System

Database Management Systems (DBMS) are specially designed software which is used to create and maintain a database. It acts as an interface between users and a database or multiple databases. DBMS is comprised of tables that made up of rows called records and columns called fields.

The important processes catered by existing DBMS are as below:

• Defining or constructing a data structure which is also called as data definition such as creating a table, deleting a table or modifying the existing one.
• Updating like inserting a record into a table, deleting or modifying a record.
• Retrieval or extracting information from the database by user queries for user applications, reporting or any other business purposes.
• Administration includes the activities like enforcing data security, maintaining data integrity, data backup and recovery, granting & revoking accesses, performance monitoring, disaster management etc. These activities are generally carried out by a DBA (database administrator).

SOME OF THE DATABASE MANAGEMENT SYSTEM ARE

(1) Microsoft Access : This is the database management system developed by Microsoft. It stores data in its own format based on the Access Jet Database Engine. It also has the facilities like importing or linking directly to data stored in other databases and applications.

(2) MySQL : MySQL is open source database management system, one of the most popular dbms on the web. It is reliable, fast and also flexible.

(3) Oracle : Developed by Oracle corporation. It is object relational database management system. The original version of Oracle software was developed by Software Development Laboratories (SDL). Oracle is regarded to be one of the safe DBMS.

(4) Microsoft SQL Server : Microsoft developed this relational database server. The primary function of this software is to store and retrieve the data as requested by other applications, whether those applications are on the same computer or running on other computers across the network (including internet).

COMPONENTS OF DATABASE SYSTEM

The database system can be divided into four components.

• Users : Users may be of various type such as DB administrator, System developer and End users.
• Database application : Database application may be Personal, Departmental, Enterprise and internal.
• DBMS : Software that allow users to define, create and manages database access. Ex : Mysql, Oracle etc.
• Database : Collection of logical data.

DATABASE MODEL

A Database model defines the logical design of data. The model describes the relationships between different parts of the data. In history of database design, three models have been in use.

• Hierarchical Model : In this model each entity has only one parent but can have several children. At the top of hierarchy there is only one entity which is called Root.
• Network Model : In the network model, entities are organised in a graph, in which some entities can be accessed through several path.
• Relational Model : In this model, data is organised in two-dimensional tables called relations. The tables or relation are related to each other.

ENTITY RELATIONSHIP MODEL

E-R model is a very popular conceptual data model which is used to develop conceptual design of databases. This data model describes or perceives the real world data in form of entities. The E-R Model: The enterprise is viewed as set of

• Entities
• Relationships among entities

Symbols used in E-R Diagram

• Entity – rectangle
• Attribute – oval
• Relationship – diamond
• Link - line

An entity is a basic unit of E-R model which is an object or a thing in real world having independent existence. An entity may be concrete and a physical existence (e.g.: person, place) or it can be abstractor conceptual existence like loan, course. Entity is an object that is involved in the enterprise and that be distinguished from other objects.

• Can be person, place, event, object, concept in the real world
• Can be physical object or abstraction
• Ex: “John”, “CSE305”

ENTITY SET : It is a collection of entities of a particular entity type at any point of time. For example: A firm is having many employees, these are defined as entities(e1, e2, e3, .en) and all these entities are having same attributes under entity type employee. The set of students(e1,e2,e3……) is entity set.

TYPES OF ENTITY SET

Weak : 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.

Example :We depict a weak entity set by double rectangles. We underline the discriminator of a weak entity set with a dashed line. payment-number – discriminator of the payment entity set

Strong : An entity set that has a primary key is called as Strong entity set. Consider an entity set Payment which has three attributes: payment_number, payment_date and payment_amount. Although each payment entity is distinct but payment for different loans may share the same payment number. Thus, this entity set does not have a primary key and it is an entity set.

Difference between Strong Entity set and Weak Entity set

• Strong Entity Set: It has its own primary key. / Weak Entity Set: I does not save sufficient attributes to form a primary key on its own.
• Strong Entity Set: It is represented by a rectangle. / Weak Entity Set: It is represented by a double rectangle.
• Strong Entity Set: It contains a primary key represented by an underline. / Weak Entity Set: It contains a Partial Key or discriminator represented by a dashed underline.
• Strong Entity Set: The member of strong entity set is called as dominant entity set. / Weak Entity Set: The member of weak entity set is called as subordinate entity set.
• Strong Entity Set: The Primary key is one of its attributes which uniquely identifies its member. / Weak Entity Set: The Primary Key of weak entity set is a combination of partial key and primary key of the strong entity set.
• Strong Entity Set: The relationship between two strong entity set is represent by a diamond symbol. / Weak Entity Set: The relationship between one strong and a weak entity set is represented by a double diamond sign. It is known as identifying relationship.
• Strong Entity Set: The line connecting strong entity set with the relationship is single. / Weak Entity Set: The line connecting weak entity set with the identifying relationship is double.
• Strong Entity Set: To participation in the relationship may or may not exist. / Weak Entity Set: Total participation in the identifying relationship always exists.

VALUE SET OR DOMAIN VALUES

A set of possible values that can be assigned to a given attribute in individual entity. For example, the attribute employee name in employee entity type can have character data and integer value. Hence the values in this attribute will be a non-integer domain.

ENTITY TYPE

It is the set of similar objects or a category of entities; they are well defined

• A rectangle represents an entity set
• Ex: students, courses
• We often just say “entity” and mean “entity type”

ATTRIBUTE

It describes one aspect of an entity type; usually [and best when] single valued and indivisible (atomic)

• Represented by oval on E-R diagram
• Ex: name, maximum enrollment
• May be multi-valued – use double oval on E-R diagram
• May be composite – attribute has further structure; also use oval for composite attribute, with ovals for components connected to it by lines
• May be derived – a virtual attribute, one that is computable from existing data in the database, use dashed oval. This helps reduce redundancy

DOMAIN

It is the possible values of an attribute.

• Note that the value for an attribute can be a set or list of values, sometimes called “multi-valued” attributes
• This is in contrast to the pure relational model which requires atomic values
• E.g., (111111, John, 123 Main St, (stamps, coins))

FUNCTIONS OF DBMS

• Provides data independence
• Concurrency Control
• Provides Recovery services
• Provides Utility services
• Provides a clear and logical view of the process that manipulates data.

ADVANTAGES OF DBMS

• Segregation of application program.
• Minimal data duplicacy.
• Easy retrieval of data
• Reduced development time and maintenance need.

DISADVANTAGES OF DBMS

• Complexity
• Costly
• Large in size

RDBMS (RELATIONAL DATABASE MANAGEMENT SYSTEM)

RDBMSs have become a predominant choice for the storage of information in new databases used for financial records, manufacturing and logistical information, personnel data, and much more since the 1980s. Relational databases have often replaced legacy hierarchical databases and network databases because they are easier to understand and use. However, relational databases have been challenged by object databases, which were introduced in an attempt to address the object-relational impedance mismatch in relational database, and XML databases.

Relational data model describes the world as “a collection of inter-related relations (or tables).

Relational databases are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways. An important feature of relational systems is that a single database can be spread across several tables. This differs from flat-file databases, in which each database is self-contained in a single table.

Almost all full-scale database systems are RDBMS’s. Small database systems, however, use other designs that provide less flexibility in posing queries.

KEYS

An important constraint on the entities is the key. The key is an attribute or a group of attributes whose values can be used to uniquely identify an individual entity in an entity set. Types of keys are described below:

1. Candidate Key

• a simple or composite key that is unique and minimal
• unique – no two rows in a table may have the same value at any time
• minimal – every column must be necessary for uniqueness
• For example, for the entity Employee (EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID) Possible candidate keys are EID, SIN
• FirstName and Last Name – assuming there is no one else in the company with the same name, Last Name and DepartmentID – assuming two people with the same last name don’t work in the same department.
• EID, SIN are also candidate keys

2. Composite Key

• Composed of more than one attribute.
• For example, as discussed earlier. First Name and Last Name – assuming there is no one else in the company with the same name, Last Name and Department ID – assuming two people with the same last name don’t work in the same department.
• A composite key can have two or more attributes, but it must be minimal.

3. Primary Key

• A candidate key is selected by the designer to uniquely identify tuples in a table. It must not be null.
• A key is chosen by the database designer to be used as an identifying mechanism for the whole entity set. This is referred to as the primary key. This key is indicated by underlining the attribute in the ER model.
• For example Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID) – EID is the Primary key.

4. Secondary Key

• An attribute used strictly for retrieval purposes (can be composite), for example: Phone number, Last Name and Phone number, etc.
• All other candidate keys not chosen as the primary key
• An attribute in one table that references the primary key of another table OR it can be null.
• Both foreign and primary keys must be of the same data type
• For example: Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID) – DepartmentID is the Foreign key.

5. Foreign Key

• An attribute in one table that references the primary key of another table OR it can be null.
• Both foreign and primary keys must be of the same data type
• For example: Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID) – DepartmentID is the Foreign key.

6. Unique Key

It is used to uniquely define the attribute of each row.

NULLS

This is a special symbol, independent of data type, which means either unknown or inapplicable. (it does not mean zero or blank)

• No data entry
• Not permitted in primary key
• Should be avoided in other attributes
• Can represent:
  • An unknown attribute value
  • A known, but missing, attribute value
  • A “not applicable” condition
• Can create problems when functions such as COUNT, AVERAGE, and SUM are used
• Can create logical problems when relational tables are linked

NOTE : The result of a comparison operation is null when either argument is null. The result of an arithmetic operation is null when either argument is null (except functions which ignore nulls)

SOME IMPORTANT TERMS IN DBMS

SCHEMA of a database system is its structure described in a formal language supported by the database management system (DBMS) .

DATA MINING sometimes called data or knowledge discovery) is the process of analyzing data from different perspectives .

TABLES: Refers to data arranged in rows and columns. A spreadsheet, for example, is a table. In relational database management systems, all information is stored in the form of tables.

FIELDS : The smallest unit of information about a record in a datbas is called field.

RECORD: A complete set of information. Records are composed of fields, each of which contains one item of information. A set of records constitutes a file.

QUERIES: In database management systems, query by example (QBE) refers to a method of forming queries in which the database program displays a blank record with a space for each field. You can then enter conditions for each field that you want to be included in the query.

FORM : A program that generally has more user friendly interface than a DBMS is called a form

REPORTS : A formatted and organized presentation of data. Most database management systems include a report writer that enables you to design and generate reports.

CARDINALITY: In database design, the cardinality or fundamental principle of one data table with respect to another is a critical aspect. The relationship of one to the other must be precise and exact between each other in order to explain how each table links together. In the relational model, tables can be related as any of “one-to-many” or “many-to-many.” This is said to be the cardinality of a given table in relation to another.

DATA REDUNDANCY: Data redundancy occurs in database systems which have a field that is repeated in two or more table

DATA INTEGRITY: Data integrity refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design

DATA RELIABILITY: the accuracy and completeness of computer-processed data, given the uses they are intended for

DATA CONSISTENCY: Consistency, in the context of databases, states that data cannot be written that would violate the database’s own rules for valid data. If a certain transaction occurs that attempts to introduce inconsistent data, the entire transaction is rolled back and an error returned to the user.

TUPLE : Tuple is the collection of information about the attributes of table for single instance. In simple this also can be called as a ‘row’ in a Table

ROLLBACK: The process of restoring a database or program to a previously defined state, typically to recover from an error.

IMPORTANT COMMANDS IN DATABASE:

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - add comments to the data dictionary
• RENAME - rename an object

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• MERGE - UPSERT operation (insert or update)
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

Data Control Language (DCL) statements. Some examples:

• GRANT - gives user’s access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use