Learn The design process In DBMS

Where do databases come from?

- There may be an manual system that has reached the limits of its usefulness
- processing requirements of the organization may have proved to be beyond the capabilities of flat file system

Why is database design important?

- Look at the following Excel spreadsheet currently used Look at the following Excel spreadsheet currently used by ABC Ltd to store its course information.

- need to keep record of staff members who attend each course
- uses a list on Excel worksheet, simple arrangement (rows and columns)
- single table
- it may be better to break it up into separate lists, but lack the ability to hold cross-reference in Excel.
- Columns headed Atendee1 name and Atendee1

Problems with this approach

Redundant data

- each time that staff member attends a course it is necessary to record their department.
- This redundancy could be avoided if information about the staff member could be held in a separate list that could be crossreferenced.
- each row contains a code for the course as well as its title. This redundancy could be avoided if information about the course could be held in a separate list that could be cross-referenced.
- redundant data should not be confused with duplicate values.

Inflexibility Inflexibility
- it is necessary to impose arbitrary limits on its uses
- waste of storage space
- the ways that can analyse the data in this example are limited.

Limited sharing of data

- file is manipulated as a single unit.
- it is not possible for more than one user to update the data in
the same file at the same time.

No inherent control for data quality
- In flat file systems, it is necessary to write logic into an application to ensure the quality of the data
- such application logic may have to be rewritten every time the design of the file is changed

Changing the data can lead to unexpected results

- update anomaly , ‘every occurrences should be updated
- insertion anomaly, ‘difficult to find missing data ’
- deletion anomaly, ‘loss all the information when it deleted'

Learn to Create Data Model in DBMS E-R model

The Entity Relationship Model in DBMS
- The Entity-Relationship model is a set of concepts and graphical symbols that can be used to create conceptual schemas
Versions/ Evolution of the E-R Model
- Original E-R model by Peter Chen (1976)
- Extended E-R model (1986): added subtypes, now the most widely used E-R model
- Information Engineering/IE model (also called the Crow’s Foot model) developed by James Martin in 1990
- IDEF1X (1994) : national standard by the National Institute of Standards and Technology
- Additionally:
Unified Modeling Language (UML) Style E-R Models
Semantic Object Data Models (SOMs)
The Four Major Components of the E-R Model
• Entities (really entity sets)
• Attributes
• Relationships
• Identifiers
The Geometric Symbols in an E-R Model
- Entities are represented by rectangles.- Attributes are represented by ovals, that are connected to the entity by a straight line.
- Relationships are represented by diamond shaped

Entity Sets

- A database can be modeled as:
• a collection of entities,
• relationship among entities.
- An entity is an object that exists and is distinguishable from other objects.
• Eg. specific person, company, event, plant
- Entities have attributes
• Eg. people have names and addresses
- An entity set is a set of entities of the same type that share the same properties.
• Eg. set of all persons, companies, trees, holidays
Note: There are usually many instances of an entity

Entity Sets customer and loan












Attributes

- An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set.
- Description of the entity’s characteristics







- Domain – the set of permitted values for each attribute
- Attribute types:
• Simple and composite attributes.
• Single-valued and multi-valued attributes
E.g. multivalued attribute: phone-numbers
• Derived attributes Can be computed from other attributes
E.g. age, given date of birth

Composite Attributes

Who are the Database Users?

Database Users
- Users are differentiated by the way they expect to interact with the system
- Application programmers – interact with system through DML calls
- Sophisticated users – form requests in a database query language
- Specialized users – write specialized database applications that do not fit into the traditional data processing framework
- Naïve users – invoke one of the permanent application programs that have been written previously
E.g. people accessing database over the web, bank tellers, clerical staff

Database Administrator
- Coordinates all the activities of the database system; the database administrator has a good understanding of the enterprise’s information resources and needs.
- Database administrator's duties include:
• Schema definition
• Storage structure and access method definition
• Schema and physical organization modification
• Granting user authority to access the database
• Specifying integrity constraints
• Acting as liaison with users
• Monitoring performance and responding to changes in requirements

Transaction Management
- A transaction is a collection of operations that performs a single logical function in a database application
- Transaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and
transaction failures.
- Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database.

Storage Management
- Storage manager is a program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system.
- The storage manager is responsible to the following tasks:
• interaction with the file manager
• efficient storing, retrieving and updating of data

Overall System Structure



Application Architectures
Two-tier architecture: E.g. client programs using ODBC/JDBC to communicate with a database
Three-tier architecture: E.g. web-based applications, and applications built using “middleware”

Language In use DBMS

Data Definition Language (DDL)
- Specification notation for defining the database schema
• E.g. create table account (account-number char(10),
balance integer)
- DDL compiler generates a set of tables stored in a data dictionary
- Data dictionary contains metadata (i.e., data about data) database schema
Data storage and definition language
• language in which the storage structure and access methods
used by the database system are specified
• Usually an extension of the data definition language

Data Manipulation Language (DML)
- Language for accessing and manipulating the data organized by the appropriate data model
• DML also known as query language
- Two classes of languages
• Procedural – user specifies what data is required and how to get those data
• Nonprocedural – user specifies what data is required without specifying how to get those data
- SQL is the most widely used query language

SQL
- Developed at IBM research labs at San Lose, Ca. in 1970’s
• D.Chamberlin was the leader in the research group
- Dominant data base language on mainframe computers, but available on mini and PC based DBMS
- ANSI (American National Standards Institute approved
in 1992 (SQL 92); SQL 93 exists but not popular

Use Of Data Base

Uses of database
- banking systems that keep information about customers and their accounts
- point of sales systems in shops that process the purchase of goods
- inventory systems
- reservation systems for booking airline flights and other travels
- payroll systems that process salaries and benefits
- accounting systems that produce invoices, statements and other company accounts
- manufacturing systems: many products are now produced by machines that are controlled by computers
- Universities: registration, grades
- Databases touch all aspects of our lives

Database Management System (DBMS)

- DBMS contains information about a particular enterprise
- DBMS provides an environment that is both convenient and efficient to use.
- computer software with the capability to store data in an integrated structured format and to enable users to retrieve, manipulate and manage the data

Introduction to Data Base

Keeping Records

- Mankind has been keeping records since the dawn of civilization (purely pictorial)
- then into the alphabetic systems
- quantitative (numerical and financial) information was kept in lists.
- later these lists took on a more structured form (rows and columns) called ledger
- these records were maintained by hand, usually on paper, dozens of clerks
- computer has changed all this
- almost every organization now keeps its records in electronic form and process them using a computer system
- army of clerks is no longer required
- introduction of the computer has led to major changes in employment pattern
- employment pattern shift away from primary industries (agriculture and manufacturing) to service
- this trend has been accelerated by the increasing use of IT (computers and electronic communications)
- at the heart of these computerized information processing systems you will find a DATABASE

Data and Information


What is Data?
- individual facts about something or somebody that have not been organized (raw data)
- a random collection of names and telephone numbers is just data
What is Information?
- data that has been organized in such a way as to be useful to somebody
- the telephone directory contains information because it has been organized
- information to one person in a particular circumstance may be simply considered as data to another

Information processing
Key aspects key aspects
- capturing data (forms for capturing data (forms for data capture )
- storing data (tables for data storage) storing data (tables for data storage)
- processing data (queries, macro and programs) processing data (queries, macro and programs)
- communicating information (Reports for communicating communicating information (Reports for communicating information)
What is Database?
- combination of software and data that are used to perform combination of software and data that are used to perform
above four key aspect of information processing above four key aspect of information processing
- computers are not the only means of storing data and computers are not the only means of storing data and producing information producing information
- An integrated collection of data organized to meet the An integrated collection of data organized to meet the needs of one or more users needs of one or more users

Levels of Abstraction
- Physical level describes how a record (e.g., customer) is stored.
- Logical level: describes data stored in database, and the relationships among the data.
Eg. type customer = record
name : string;
street : string;
age : integer;
end;
- View level: application programs hide details of data types. Views can also hide information (e.g. account balance) for security purposes.

Instances and Schemas
- Similar to types and variables in programming languages
- Schema – the logical structure of the database (a complete example will be given in SQL lecture)
• e.g., the part of the bank database consists of information about a set of customers and accounts and the relationship between them)
• Analogous to type information of a variable in a program
• Physical schema: database design at the physical level
• Logical schema: database design at the logical level
- Instance – the actual content of the database at a particular point in time
• Analogous to the value of a variable
- Physical Data Independence – the ability to modify the physical schema without changing the logical schema
• Applications depend on the logical schema
• In general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others.

Data Models
- A collection of tools for describing
• data
• data relationships
• data semantics
• data constraints
- Entity-Relationship model (we will discuss in this module)
- Relational model (we will discuss in this module)
- Other models:
• object-oriented model
• semi-structured data models
• Older models: network model and hierarchical model

Entity Relationship model
- E-R model of real world
Entities (objects)
• E.g. street block,street,parcel
Relationships between entities
• Relationship set depositor associates customers with accounts
- Widely used for database design
• Database design in E-R model usually converted to design in the
relational model which is used for storage and processing