Unit 2 Database System Concepts and Architecture PDF

Preview

Summary

unit 2...

Description

Unit 2 Database System Concepts and Architecture Instances and Schemas Database change over time as information is inserted and deleted. The collection of information stored in the database at a particular moment is called an instance of the database. The overall design of the database is called the database schema. The description of a database is called the database schema, which is specified during database design and is not expected to change frequently. The actual data in a database may change quite frequently. This instance is also called a database state or snapshot. Data Models One fundamental characteristic of the database approach is that it provides some level of data abstraction by hiding details of data storage that are not needed by most database users. Data model, so that a collection of concepts that can be used to describe the structure of a database-provides the necessary means to achieve this abstraction. By structure of a database, it means the data types, relationships, and constraints that should hold for the data. Most data models also include a set of basic operations for specifying retrievals and updates on the database. Different types of data Models are as follows: 1. Hierarchical Data Model Hierarchical databases can be found on many legacy servers, and was one of the first data models to be widely accepted and used. The most recognized and used hierarchical databases are IMS developed by IBM in 1960 and Windows Registry by Microsoft. Hierarchical database model is a structure of data organized in a tree-like model using parent/child like relationships, therefore there won't be too many relationships. In a hierarchical database, an entity type can be either a parent or a child. A tree structure is a way of representing the hierarchy of a structure in a model to display which entity is derive from either a parent or a child. In a hierarchical database, records contain groups of parent/child relationships, the structure allows repeating information using parent/child relationships: each parent can have many children but each child only has one parent (also known as a 1: many ratios). All attributes of a specific record are listed under an entity type, similar to a tree structure. For example, under a customer name (parent) would be stored the child; description of their last purchase and the date, a child under that would be the individual items purchased, the cost per item and a description of the item, and another child under that would be the items manufacturers name.

2. Network Data Model A network database model is a database model that allows multiple records to be linked to the same owner file. The model can be seen as a upside down tree where the branches are the member information linked to the owner, which is the bottom of the tree. The multiple linkages which this information allows the network database model to be very flexible. In addition, the relationship that the information has in the network database model is defined as many to many relationships because one owner can be linked to many member files and vice versa. The network database model was invented by Charles Bachman in 1969 as an enhancement of the already existing database model, the hierarchical database model. Because the hierarchical database model was highly 1

flaw, Bachman decided to create a database that is similar to the hierarchical database but with more flexibility and less defaults. The original and existing hierarchical database has one owner file linked strictly to one member file, creating a ladder affect that restricted the database to find relationships outside of its category.

3. Relational Data Model A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. A relational database allows database administrator to define relationships between these tables. These relationships enable Database administrator and these relationships are accomplished through the use of keys. The relational database was invented by E. F. Codd at IBM in 1970.

4. Object oriented Data Model The object oriented database (OOD), which was first developed in the late 1980s, groups data items into complex items called objects. These objects, which parallel the object structure used in object oriented programming, can represent anything: a product, an event. An object is defined by its characteristics; attributes can be text, sound, video, graphics. Example of attribute may be color, size, style, quantity. Object: CAR Object: Student Attributes: Attributes: RollNo Price Name Brand DOB Functions: Functions: Start() Eat() Stop() Read() Acceleration AppearExam() 2

Advantages of Object Oriented Database Model   

  

Because of its inheritance property, we can re-use the attributes and functionalities. It reduces the cost of maintaining the same data multiple times. Also, these information are encapsulated and, there is no fear being misused by other objects. If we need any new feature we can easily add new class inherited from parent class and adds new features. Hence it reduces the overhead and maintenance costs. Because of the above feature, it becomes more flexible in the case of any changes. Codes are re-used because of inheritance. Since each class binds its attributes and its functionality, it is same as representing the real world object. We can see each object as a real entity. Hence it is more understandable.

Disadvantages  

It is not widely developed and complete to use it in the database systems. Hence it is not accepted by the users. It is an approach for solving the requirement. It is not a technology. Hence it fails to put it in the database management systems.

The three-Schema Architecture The goal of this architecture is to separate the user applications and the physical database. In this architecture, schemas can be defined at the following three levels: 1. Internal Level

This level has an internal schema. This view is at the lowest level of abstracts closest to the physical storage structure of the database. It indicates how the data to be stored and describes the data structures and access to be used by the database. The internal view is expressed by the internal schema, which contains the definition of stored record, the method of representing the data fields, access aids used. 2. Conceptual Level

This level has the conceptual schema. At this level of database abstraction all database entities and the relationship, constraints among them are included. One conceptual view represents the entire database. This conceptual view is defined by conceptual schema. It describes all the records and relationship included in the conceptual view. The conceptual schema hides the details of physical storage structures and concentrates on describing entities, data types, relationships, constraints and user operations. 3. External Level

The external or user view is the highest level of abstraction where only the concerned portions of the database to a user or application program are included. Each external view is described by means of an 3

external schema. The external schema consists of the definition of logical and relationship in external view. The three-schema architecture is a convenient tool with which the user can visualize the schema levels in a database system. These three schemas are only descriptions of data; the only data that actually exists is at the physical level. The data retrieved from the database must be formatted according to the user requirement. If not then the data need to be reformatted. The processes of transforming requests that results between various levels are called mapping.

Data Independence The 3-shcema architecture can be used to further explain the concept of data independence. A database system normally contains a lot of data in addition to users’ data. For example, it stores data about data, known as metadata, to locate and retrieve data easily. It is rather difficult to modify or update a set of metadata once it is stored in the database. But as a DBMS expands, it needs to change over time to satisfy the requirements of the users. If the entire data is dependent, it would become a tedious and highly complex job. Metadata itself follows a layered architecture, so that when we change data at one layer, it does not affect the data at another level. This data is independent but mapped to each other. Data independence occurs because when the schema is changed at some level, the schema at the next higher level remains unchanged. We can define two types of data independence: 1. Logical Data Independence Logical data independence is the capacity to change the conceptual schema without having to change external schemas or application programs. We may change the conceptual schema to expand the database, to change constraints or to reduce the database. 2. Physical Data Independence Physical data independence is the capacity to change the internal schema without having to change the conceptual schema. Hence, the external schemas need not be changed as well. Changes to theinternal schema may be needed because some physical files had to be re-organized. Database Language Database languages are used to interact with the database. There are two different types of database language, DDL and DML where DDL to specify the database schema and DDL to express database queries and updates. In practice, the DDL and DML are not two separate languages, instead they simply form parts of a single database language, such as the widely used SQL language. 1. Data definition language Data Definition Language (DDL) is a standard for commands that define the different structures in a database. DDL statements create, modify, and remove database objects such as tables, indexes, and users. Common DDL statements are as follows: o CREATE - to create objects in the database o ALTER - alters the structure of the database o DROP - delete objects from the database o TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed o COMMENT - add comments to the data dictionary o RENAME - rename an object 2. Data manipulation language The data manipulation language (DML) is used to manipulate the database. In data manipulation, data is retrieved, inserted, updated, and deleted to and from the database. Common DML statements are follows: 4

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 There are basically two types of DML, they are:  Procedural DMLs require a user to specify what data are needed and how to get those data.  Declarative DMLs require a user to specify what data are need without specifying how to get those data. o o o o o o o o

Actors of Database and DBMS 1. System Analyst and Application Programmers System analyst determines the requirements of end users, especially naïve and parametric end users and develops specifications for canned transactions that meet requirements. Application programmers implement these specifications as programs. 2. Database Administrator Nowadays, there are many companies from different business sectors who prefer to keep their data stored into particular databases. These databases are huge storage units, which are used for stacking away confidential company data, and the information can be obtained when needed by company employees. However, for efficient functioning of these huge databases, they are required to be maintained in a proper manner. And this is where the services of database administrators come in use. Database administrators are professionals from the Information Technology (IT) sector, and are experts in maintaining and coordinating the functioning of databases. Their services are availed by companies, just to ensure that their databases are working fine. These IT professionals are known by different names, such as 'database programmers' and 'database coordinators'. This database administrator is the individual who is responsible for maintaining the database. However, along with database support, they might also provide additional IT-related services to the employer, if there comes such a requirement. Qualities of database administrator  Knowledge of database installation and integration.  Knowledge of database troubleshooting database and database applications.  Knowledge of database administration, database monitoring and database maintenance.  Knowledge of different RDBMS and SQL.  The database administrator must be able to enforce and apply proper change management and quality assurance processes for changes being implemented into the database environment.  Good communication and interpersonal skills to work well with team or project member.  Knowledge of advance database installation and integrations.  Knowledge of database configuration, security, installation and tuning.  Knowledge of Computer network  Knowledge of operating systems. Duties and responsibilities of database administrator  Analyses and creates installation procedures for complex database installations, database patches, database upgrades and application installs.  Perform routine administrative and maintenance tasks for the database environments e.g. backups and recovery, database refreshes, data fixes etc.  Monitors development, test and production database to identify and report any backups, performance, space usage and security issues and recommend solutions.  Maintains the integrity and availability of the database environment. 5

   

Recommends improvements for supporting database environments which include consolidating database servers, implementing database security enhancements, decommissioning old database. Provides consultation and input into, database server sizing, database design and capacity planning for current and new databases and applications. Provides expertise on the necessary tools for reporting on all database metrics and analyzing metrics to make improvements to the database environments. Actively participates in a project team to identify the technical requirements and specifications for any new database to development and installation.

3. End Users End users are the people whose jobs require access to the database for querying, updating, and generating reports. The database primarily exists for their use. There are several categories of end users.  Casual End Users These users occasionally access the database, but they may need different information each time. They use a sophisticated database query language to specify their requests and are typically middle/or high level managers or other occasional browsers.  Naïve or Parametric end users These users make up a sizable portion of database end users. Their main job function revolves around constantly querying and updating the database.  Sophisticated end users This includes engineers, scientists, business analysts, and others who thoroughly familiarize themselves with the faculties of the DBMS so as to implement their applications to meet their complex requirements.  Stand-alone users These users maintains personal database by using ready-made program package that provides easy-to use menu-based or graphics-based interface.

Entity Relationship Model The ER model describes data as entities, relationships, and attributes. Entity An entity is a ‘thing’ or ‘object’ in the real world that is distinguishable from all other objects. For example each person in an enterprise is an entity. An entity has a set of properties and the values for some set of properties may uniquely identify an entity. An entity set is a set of entities of the same type that share the same properties, or attributes. The set of all persons who are customers at a given bank, for example, can be defined as the entity set customer, the entity set loan might represents the set of all loans awarded by a particular bank.

6

Attributes An entity is represented by a set of attributes. Attributes are descriptive properties possessed by each member of an entity set. An attribute, as used in ER model can be characterized by the following attribute types: 1. Simple and Composite Attributes that are not divisible are called simple or atomic attributes e.g. Name of an employee. As well as some of the attributes are breakable they are known as composite e.g. address can be divided into city, district, zone and country. 2. Single-valued and Multi-valued Most attributes have a single value for a particular entity; such attributes are called single valued for example age is the single valued attribute. In some cases an attribute can have a set of values for some entity e.g. color of car, these kinds of attributes are called multi-valued attribute. 3. Stored and Derived In some cases tow or more attribute values are related e.g. age and date of birth. For particular person entity, the value of Age can be determined from the current data and the person’s date of birth. Hence Date of Birth is known as Stored attribute and age is known as derived attribute. 4. Null Values In some cases a particular entity may not have an applicable values for an attribute. E.g. the apartment number attribute of an address applies only to addresses that are in apartment building but not applicable for those who lives in single house. Keys A database key is a attribute utilized to sort and/or identify data in some manner. Each table has a primary key which uniquely identifies records. Foreign keys are utilized to cross-reference data between relational tables. a. Primary key This is the attribute that is set to the field and if any field is set as the primary key then that field never accept null value as well as repeating value. This attribute is used to uniquely identify the data. b. Foreign key If any field is set as a primary key in master table and same field is also used in another table then the primary key field in another table is said as the foreign key. This key is used for referential integrity. c. Unique key 7

Unique key is unique in nature. If any field is set as a unique then that key never receives repeating value but that key can receive the null value. d. Composite Key A compound key is a key that consists of two or more simple keys that uniquely identify an entity occurrence. Sometimes single field is not sufficient to uniquely identify the data in that case composite key is used. Relationship A relationship is an association among several entities. Whenever an attribute of one entity type refers to another entity type, some relationship exists.

In the above figure the attribute Manager of DEPARTMENT refers to an employee who manages the department; the attribute ControllingDepartment of DEPARTMENT refers to an employee who manages the department; the attribute ControllingDepartment of PROJECT refers to the department that controls the project; the attribute Supervision of EMPLOYEE refers to the another employee; the attribute Department of EMPLOYEE refers to the departmentfor which the employee works and so on. In the ER model, these references should not be represented as attribute but as relationship. Relationship Degree The degree of a relationship type is the number of participating entity types. Hence in the below figure WORKS_FOR relationship is of degree two. A relationship type of degree two is called binary and one of degree three is called ternary. An example of a ternary relationship is SUPPLY shown in next figure.

8

Binary Degree

Ternary Degree

Roles name and Recursive relationship Each entity type that participates in a relationship type plays a particular role in the relationship. The role name signifies the role that a participating entit...