Database System EC601
MRS. ISMA SHAMSURIA BINTI ISMAIL Department of Electrical Engineering (LEVEL 1) The Polytechnic of Merlimau, Melaka 019-4211728 ext:1163
EC601 : COURSE SYNOPSIS DATABASE SYSTEM course offers a comprehensive coverage of basic of basic concept and application of data manipulation. Student will learn the fundamental concepts and techniques for designing and developing database and manipulating data using Structured Query Language (SQL). Course Outline
EC601 : COURSE SYNOPSIS DATABASE SYSTEM course offers a comprehensive coverage of basic of basic concept and application of data manipulation. Student will learn the fundamental concepts and techniques for designing and developing database and manipulating data using Structured Query Language (SQL). Course Outline
EC601 : LEARNING OUTCOMES NO
COURSE LEARNING OUTCOMES (CLO)
1
Explain the basic concepts of database model using entity-relationship diagram and translating completed data models by applying normalization technique in logical database designs.
2
3
LEARNING PLO DOMAINS
PLO 1 Apply Structured Query Language (SQL) for PLO database manipulation using a database 2 management system in practical works PLO inclusive of a report report within within stipulated time 9 frame. Demonstrate problem Demonstrate problem solving skill in developing new database applications based on an assigned problem.
C4
P4
A3
EC601 : ASSESSMENT CLO
Types Of Assessment
Percentage (%)
1. CONTINUOUS ASSESSMENT (CA)
50
CLO1
i. Quiz (minimum 4)
20
CLO1
i. Theory Test (minimum 1)
20
CLO1, CLO2, CLO3
i. Practical Work (minimum 6)
50
CLO3
i. Presentation (minimum 1)
10
1. FINAL EXAMINATION (FE) Total
50 100
CHAPTER ONE
Introduction To Database System CLO 1 : Explain the basic concepts of database model using entity-relationship diagram and translating completed data models by applying normalization technique in logical database designs.
DATA VS INFORMATION DATA are raw facts used to indicate that the facts have not yet
been processes. - Data can be recorded and that have implicit meaning. For example, consider the names, telephone numbers, and addresses of the students. INFORMATION is produced by processing data used to reveal
the meaning of data. - For example, student information record including the names, telephone numbers, and addresses of the students.
INTRODUCTION DATABASE
– A collection of data stored in a standardized format, designed to be shared by multiple users. DATABASE MANAGEMENT SYSTEM (DBMS)
– Software that defines a database, stores the data, supports a query language, produces reports, and creates data entry screens.
DATA VS INFORMATION A DATABASE has some source from which data is derived,
some degree of interaction with events in the real world, and an audience that is actively interested in its contents. The end users of a database may perform business
transactions (for example, a students pays a fee) or events may happen (for example, a polytechnic has a student) that cause the information in the database to change.
LEVELS OF DATA No
Level
1.
Character
Description
2.
Field
3.
Record
4.
File
5.
Database
basic logical data element. It consists of a single alphabetic, numeric , or other symbol .
It consists of a grouping of characters. A data field represents an attribute (a characteristic or quality) of some entity (object, person, place, or event). A group of the related fields of data Represents a collection of attributes that describe an entity. A group of related records Frequently classified by the application for which they are primarily used Also classified by their permanence, for example, a master file versus a transaction file. An integrated collection of logically related records or objects. A database consolidates records previously stored in separate f files into a common pool of data records that provides data for many applications.
DRAWBACKS OF OLD FILE METHOD Uncontrolled Duplication Wastes space Hard to update all files Inconsistent data Inflexibility Hard to change data Hard to change programs Limited data sharing Poor enforcement of standards Poor programmer productivity Excessive program maintenance
DATABASE TERMINOLOGIES
Database Management System (DBMS)
File-based System
Database System
DBMS
DBMS 1. DBMS - a computer software program that enables users to access, create and maintain a database. 2. DBMS is also the combination of data, hardware, software and users to help an enterprise manage its operational data. 3. The main function of a DBMS - to provide efficient and reliable methods of data retrieval to many users. 4. The general-purpose of DBMS - facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications.
DBMS No 1.
Process Defining
Description specifying the data types, structures, and constraints of the data to be stored in the database. Data files - files that store the database information Meta-data - such as index files and data dictionaries, store administrative information.
2.
Constructing
process of storing the data on some storage medium that is controlled by the DBMS.
3.
Manipulating
functions such as querying the database to retrieve specific
data, updating the database to reflect changes in the miniworld, and generating reports from the data. 4.
Sharing
allows multiple users and programs to access the database
DBMS Advantages of Database Approach Minimal data redundancy. Data consistency. Integration of data. Sharing of data. Enforcement of standards. Ease of application development. Uniform security, privacy and integrity. Data independence.
DBMS DATABASE MANAGEMENT APPROACH Data is most important
Data defined first
Standard format
All Data
Access through DBMS
Queries, Reports, Forms Application Programs 3GL Interface
Data independence
DBMS
Change data definition without changing code
Alter code without changing data
Move/split data without changing code
Queries Program1 Program2 Reports
DBMS EXAMPLES OF COMMERCIAL SYSTEMS SOFTWARE Oracle Ingres Informix (Unix) DB2, SQL/DS (IBM) Access (Microsoft) SQL Server (Microsoft +) Many older (Focus, IMS, ...) Many limited PC (dBASE, Paradox, …)
FILE_BASED SYSTEM A file based system - a collection of application
programs that perform services for the end-users, usually the production of reports. Each program within a file based system defines and
manages its own data - there are limits as to how that data can be used or transported.
FILE_BASED SYSTEM Were developed as better alternatives to paper based
filing systems. Files stored on computers - the data could be accessed
more efficiently. Common practice for larger companies - each of its
departments looking after its own data.
FILE_BASED SYSTEM
FILE_BASED SYSTEM
FILE-BASED SYSTEM The PROBLEMS of file based system are: Data separation and isolation Data dependence Data duplication Incompatible data (different file formats) Lack of flexibility in organizing and querying the data Increased number of different application programs
DATABASE SYSTEM Database system is a system to achieve an organized,
store a large number of dynamical associated data, facilitate for multi-user accessing to computer hardware, software and data, that it is a computer system with database technology. The database approach emerged to resolve the problems
with the file-based approach.
DATABASE SYSTEM Advantages of database systems are: Sharing of data
Improved data access and
Consistency of data
system performance
Integrity of data
Potentially increased
Security of data Data independence Allows for more analysis of
the same amount of data
productivity Increased concurrency Improved data backups and
recovery
DATABASE SYSTEM
DATABASE SYSTEM ENVIRONMENT
DATABASE SYSTEM
CHARACTERISTICS OF DATABASE SYSTEM The main characteristics of the database approach versus the file processing approach are the following:
1.
Self-describing nature of a database system
2.
Insulation between programs and data, and data abstraction
3.
Support of multiple views of the data
4.
Sharing of data and multiuser transaction processing
CHARACTERISTICS OF DATABASE SYSTEM 1. SELF-DESCRIBING NATURE OF A DATABASE SYSTEM : Database System contains not only the database itself
but also the descriptions of data structure and constraints (meta-data) - used by the DBMS software or database users if needed. This separation makes database system totally different
from traditional file-based system in which data definition is a part of application programs.
CHARACTERISTICS OF DATABASE SYSTEM 2. Insulation between Program and Data In the filed base system, the structure of the data files
is defined in the application programs so if user want to change the structure of a file, all the programs access to that files might need to be changed. In database approach, data structure is stored in the
system catalog not in the programs so such changes might not occurs.
CHARACTERISTICS OF DATABASE SYSTEM 3. Support multiple views of data A view is a subset of the database which is defined
and dedicated for particular users of the system. Multiple users in the system might have different
views of the system. Each view might contains only the interested data of
an user or a group of user.
CHARACTERISTICS OF DATABASE SYSTEM 4. Sharing of data and Multiuser system A multiuser database system must allow multiple
users access the database at the same time. As the result, the multiuser DBMS must have
concurrency control strategies to ensure that several user try to access the same data item at a time do so in the manner so that the data always be correct.
APPLICATION OF DATABASE SYSTEM Digital libraries and digital
publishing Finance Sales Human resources Education Credit card transactions E-commerce Health care information
systems and electronic patient record
Airlines and railways Banking Telecommunications
TYPES OF DATABASE SYSTEM 1. PERSONAL DATABASE Also known as a SINGLE USER Database / a
DESKTOP Database Supports only one user at a time User B or C must wait until user A has completed his
database work.
TYPES OF DATABASE SYSTEM 2. WORKGROUP DATABASE Also known as a MULTIUSER Database Supports a relatively small number of users (<50) A specific department within an organization using the
same database – DEPARTMENT DATABASE
TYPES OF DATABASE SYSTEM 3. ENTERPRISE DATABASE The database used by the entire organization Supports MANY users (>50) across many departments
The DBMS acts as an interface between what two components of an enterprise-class database system? A. Database application and the database B. Data and the database C. The user and the database application D. Database application and SQL
A
The following are components of a database except ________ . A. user data B. metadata C. reports D. indexes
C
An application where only one user accesses the database at a given time is an example of a ________ . A. single-user database application B. multiuser database application C. e-commerce database application D. data mining database application
A
An on-line commercial site such as Amazon.com is an example of a ________ . A. single-user database application B. multiuser database application C. e-commerce database application D. data mining database application
C
Metadata enables database designers and users to do all of the following except: A. sample data. B. understand what data exist. C. what the fine distinctions are between similar data items. D. what the data mean.
A
A database management system (DBMS) is a: A. hardware system used to create, maintain, and provide controlled access to a database. B. hardware system used to create, maintain, and provide uncontrolled access to a database. C. software system used to create, maintain, and provide controlled access to a database. D. software system used to create, maintain, and provide uncontrolled access to a database.
C
Because it contains a description of its own structure, a database is considered to be ________ . A. described B. metadata compatible C. self-describing D. an application program
C
The following are functions of a DBMS except ________ . A. creating and processing forms B. creating databases C. processing data D. administrating databases
A
Structured data may include which of the following? A. Photo image B. Video clip C. Dates D. None of the above.
C
Helping people keep track of things is the purpose of a(n) ________ . A. database B. table C. instance D. relationship
A
Some advantages of the database approach include all, but: A. minimal data redundancy. B. improved data consistency. C. improved data sharing. D. program-data dependency.
D
An Enterprise Resource Planning application is an example of A ________ . A. single-user database application B. multiuser database application C. e-commerce database application D. data mining database application
B
1. In a database, data is stored in spreadsheets which have rows and columns.
2. A database has data and relationships. 3. In an enterprise-class database system, business users interact directly with database applications, which directly access the database data.
4. Applications are programs that interact directly with the database.
5. A database has a built-in capability to create, process and administer itself.
6. A database design may be based on existing data. 7. A workgroup typically comprises between 25 and 100 persons.
DATA MODEL MODEL A Model
is representation of reality, ’real world’ objects and
events and their associations. A database
model is an organizing principle that specifies
particular mechanism of data storage and retrieval.
The model explains, in terms of services available to an interfacing application, how to access a data element when other related elements are known.
DATA MODEL Data Model is a collection of concepts that can be used
to describe the structure of database. Structure of database means data types, relationships
and constraints. In addition, most data model include a set of basic
operations for specifying retrievals and modifications on the database. Data Model provides a means to achieve Data
Abstraction. Data Abstraction is refers to the hiding of certain details
of how the data are stored and maintained. With several levels of abstraction, the user’s view of the database is simplified and this leads to the improved understanding of data.
DATA ABSTRACTION The major purpose of a database system is to provide
users with an abstract view of the system. Complexity should be hidden from database users. There are 3 levels of abstraction: Levels of Abstraction
Description
Physical level
how the data is stored physically and where it is stored in database.
Logical level
what information or data is stored in the database (like what is the data type or what is format of data)
View level
end users work on view level. if any amendment is made it can be saved by other name.
LEVELS OF ABSTRACTION • Views describe how users see the data. • Conceptual schema defines logical structure • Physical schema describes the files and indexes used.
Users
View 1
View 2
View 3
Conceptual Schema Physical Schema
DB
DATA MODEL
Purpose of Data Model
To represent data.
To make the data understandable.
Types of Data Models 1.
Object Based Data Models
2.
Physical Data Models
3.
Record Based Logical Data Models
TYPES OF DATA MODEL 1. Object Based Data Models
It use concepts such as entities, attributes and relationships.
Types of Object Based Data Models: a. Entity Relationship (ER) b. Object Oriented c. Semantic d. Functional
TYPES OF DATA MODEL a. The Entity Relationship (ER) Model Based on a perception of the world as consisting of a collection of basic objects (entities) and relationships among these objects.
An entity is a distinguishable object that exists. Each entity has associated with it a set of attributes describing it. A relationship is an association among several entities.
The set of all entities or relationships of the same type is called the entity set or relationship set.
Another essential element of the E-R diagram is the mapping cardinalities , which express the number of entities to which another entity can be associated via a relationship set.
Database System Design Customer(CustomerID, Name, Address, …) SalesPerson(EmployeeID, Name, Commission, … ) Order(OrderID, OrderDate, CustomerID, EmployeeID, … ) OrderItem(OrderID, ItemID, Quantity, Price, … ) Item(ItemID, Description, ListPrice, …)
User views of data.
Conceptual data model.
Class diagram that shows business entities, relationships, and rules.
Implementation (relational) data model. List of nicelybehaved tables. Use data normalization to derive the list.
Physical data storage. Indexes and storage methods to improve performance.
TYPES OF DATA MODEL The Entity Relationship (ER) Model sample diagram
TYPES OF DATA MODEL b. The Object-Oriented Model Based on a collection of objects - class, like the E-R model.
An object contains values stored in instance variables within the object.
Unlike the record-oriented models, these values are themselves objects.
Thus objects contain objects to an arbitrarily deep level of nesting.
An object also contains bodies of code that operate on the object. These bodies of code are called methods.
OBJECT-ORIENTED DBMS (OO) Order
OrderID CustomerID … NewOrder DeleteOrder … OrderItem
OrderID ItemID … OrderItem DropOrderItem …
Customer
CustomerID Name … Add Customer Drop Customer Change Address Item
ItemID Description … New Item Sell Item Buy Item …
Government Customer Commercial
ContactName Customer ContactPhone ContactName Discount, … ContactPhone … NewContact NewContact
TYPES OF DATA MODEL c. Semantic ABSTRACTIONS: • CLASSIFICATION • AGGREGATION •
GENERALIZATION
TYPES OF DATA MODEL d. Functional
Provides an unified approach to manipulation both data and procedures.
Main idea - is a definition of all components of an information system in the form of functions.
Example: defines data objects, attributes and relationships as so-called database functions.
A Functional
Data Manipulation Manipulation Language Language is a number of data manipulation functions which can be applied to database functions.
Users are provided with a special mechanism which is called Lambda Calculus to define their own functions which can be seamlessly combined with database and data manipulation functions mentioned above.
TYPES OF DATA MODEL
Functional Data model Diagram
TYPES OF DATA MODEL 2. Physical Data Models
It describe how data is stored in the computer , representing information such as record structures, record ordering and access paths.
Less no. of models are there.
Are
used to describe describe data at the lowest level .
Very few models, e.g.
Unifying model. Frame
TYPES OF DATA MODEL
TYPES OF DATA MODEL 3. Record Based Logical Data Models
Used to specify the overall logical structure of the database.
The database consists of a no. of fixed formats of different types.
Each record type defines a fixed no. of fields having a fixed length.
3 types of record based data model: a. Hierarchical data model. b. Network data model. c. Relational data model.
TYPES OF DATA MODEL a. The Hierarchical Model
Similar to the network model.
Organization of the records is as a collection of trees, rather than arbitrary graphs.
The relational model does not use pointers or links, but relates records by the values they contain. This allows a formal mathematical foundation to be defined.
HIERARCHICAL MODEL Customers
Customer Order Items Ordered
Orders
Items Item Description 998 Dog Food 764 Cat Food
Quantity 12 11
To retrieve data, you must start at the top (customer). When you retrieve a customer, you retrieve all nested data.
HIERARCHICAL MODEL
TYPES OF DATA MODEL b. The Network Model
Data are represented by collections of records.
Relationships among data are represented by links.
Organization is that of an arbitrary graph.
NETWORK MODEL
Entry point
Customer
Order
Items Ordered
Items
Entry point
NETWORK MODEL
TYPES OF DATA MODEL c. The Relational Model
Data and relationships are represented by a collection of tables.
Each table has a number of columns with unique names, e.g. customer, account .
RELATIONAL MODEL
RELATIONAL MODEL
Customer(CustomerID, Name, … Order(OrderID, CustomerID, OrderDate, … ItemsOrdered(OrderID, ItemID, Quantity, … Items(ItemID, Description, Price, …
DATA MODEL SCHEMA AND INSTANCE Schema Logical structure of database. The description of the database which is designed in
the early stage and is not expected to change frequently. Database system have several schemas. Example: Book (Bid : char(2), Title: char(50), Author :char(20), Price: decimal(5,2))
DATA MODEL SCHEMA AND INSTANCE Instance Actual content of database at particular moment. Since information can be inserted to or deleted from
database at anytime, database changes over time. Example: Bid
Title
Author
Price
11
Franklin forgets
Bourgeois
7.00
22
Arthur and the Seventh-Inning Stretcher
Krensky
14.00
33
Midnight Wrestlers
Morgan
11.00
44
Midnight Pilot
Morgan
9.00
55
Book of Science Activities
18.00
DATABASE LANGUAGE DATA DEFINITION LANGUAGE (DDL) Specification notation for defining the database schema DDL compiler generates a set of tables stored in a data
dictionary – meta data (data about data) Data storage and definition language – special type of
DDL in which the storage structure and access method used by the database system are specified
EXAMPLE OF DDL COMMANDS
CREATE DATABASE - Creates a new database
ALTER DATABASE - Modifies the database
DROP DATABASE - Drops (deletes) a database
CREATE TABLE - Creates a new table
ALTER TABLE - Modifies the table
DROP TABLE - Drops (deletes) a table
DATABASE LANGUAGE DATA MANIPULATION LANGUAGE (DML) Language for accessing and manipulating the data
organized by appropriate data model 2 classes of language : Procedural – user specifies what data required and
how to get those data Nonprocedural – user specifies what data required
without specifying how to get those data
EXAMPLE OF DML COMMANDS
SELECT - Retrieves data from the database
INSERT - Inserts new data into the database
UPDATE - Updates existing data in the database
DELETE - Deletes existing data from the database
DATABASE LANGUAGE 4G LANGUAGE (4GL) Fourth-generation programming language (1970s-
1990) A very high level programming language and a
development environment or 'Analyst Workbench' designed with a central data dictionary system, a library of loosely coupled design patterns, a CRUD generator, report generator, end-user query language, DBMS, visual design tool and integration API.
DATABASE LANGUAGE 4G LANGUAGE (4GL) 4GL is more oriented toward problem solving and
systems engineering . Not always successful in this task, sometimes
resulting in inelegant and unmaintainable code. However, given the right problem, the use of an appropriate 4GL can be spectacularly successful. Are designed to reduce programming effort , the time it
takes to develop software, and the cost of software development
DATABASE SYSTEM ENVIRONMENT DBMS COMPONENT MODULES 1. HARDWARE All system’s physical devices Main component – computer and computer peripherals Networks
DATABASE SYSTEM ENVIRONMENT DBMS COMPONENT MODULES 2. SOFTWARE OS software - Manages all hardware components DBMS software – software – Manages the database within the
database system Applica Application tion programs and utility utility software – software – access and
manipulate data in DBMS
DATABASE SYSTEM ENVIRONMENT 3. USERS/PEOPLE applicati application on programmers, non-computer science expert and
experienced user, database designers, system administrators 4. PROCEDURES Instructions and rules that govern the design and use of
database system Used to ensure that there is an organized way to monitor
and audit both data enter and information that is generated 5. DATA Collectio Collection n of facts stored in database
DATABASE SYSTEM ENVIRONMENT DBMS COMPONENT MODULES
DATABASE SYSTEM ENVIRONMENT
Which of the following database activities determines the entities, attributes, and relationships of data? A. Conceptual data modeling B. Logical database design C. Physical database design and definition D. Database implementation
A
An entity type is which of the following? A. A major category of data about people, place, and things B. The various departments of an organization C. The application software D. The business processes the support the mission of an organization
A
Which of the following database activities require a specific knowledge of a DBMS? A. Enterprise modeling B. Conceptual data modeling C. Logical database design D. Physical database design and definition
D
