Database Design: Introduction to Database Concepts and DBMS, Lecture notes of Introduction to Database Management Systems

Download Database Design: Introduction to Database Concepts and DBMS and more Lecture notes Introduction to Database Management Systems in PDF only on Docsity!

MODELS OF

DATABASE AND

DATABASE DESIGN

LECTURE ONE

OBJECTIVES

By the end of this lecture students should be able to

understand the basic concepts of database design including

differentiating a database from a database management

system (DBMS), describing the properties of a database,

describing the database life cycle, describing the DBMS

languages and describing the ANSI-SPARC architecture

A database has the following implicit properties:

• It represents some aspects of the real world known as the mini

world or universe of discourse; changes in the mini world are

reflected in the database; this property enforces data integrity

• It is a logically coherent collection of data with some inherent

meaning; a random assortment of data is not a database; the data is

structured in such a way that it can be easily stored, manipulated

and retrieved by users

• It is designed, built and populated with data for a specific purpose;

it has an intended group of users and some preconceived

applications in which these users are interested; this property

described the relatedness of data since it describes a domain of

interest to a group of users and the users can use the data to answer

questions concerning that domain

COMPONENTS OF THE

DATABASE ENVIRONMENT

COMPUTER AIDED SOFTWARE ENGINEERING TOOLS- These are

automated tools used to design databases and application programs

REPOSITORY - This is a centralized knowledge base for all data

definitions, data relationships, screen and report formats and other

system components; it contains an extended set of metadata

important for managing databases as well as other components of an

information system

DATABASE MANAGEMENT SYSTEM- A software that interacts with the

users’ application programs and the database

DATABASE - A shared collection of logically related data and a

description of this data designed to meet the information

needs of an organisation; it is different from a repository

which contains definitions of data and the database contains

occurrences of data

APPLICATION PROGRAMS- These are computer programs that are

used to create and maintain the database and provide information to

users

DATABASE LIFE CYCLE

REQUIREMENTS ANALYSIS

It involves coming up with the database requirements which are

determined by interviewing both the producers and the users of data and

using the information to produce a formal requirements specification

LOGICAL DESIGN

It involves coming up with the conceptual schema that shows all the data

and their relationship and it is developed using techniques such as ERD or

UML; the data model constructs must be transformed into tables; steps

include Conceptual data modelling (ERD or UML); View integration (which

requires the use of ER semantics such as aggregation and generalization);

Transformation of the conceptual data model to SQL tables and

normalization of tables

PHYSICAL DESIGN

It involves the selection of indexes (access methods), partitioning and

clustering of data

DATABASE IMPLEMENTATION, MONITORING AND MODIFICATION

This is the implementation of the formal schema using the Data Definition

Language of a DBMS then the Data Manipulation Language can be used to

query and update the database as well as set up indexes and establish

constraints such as referential integrity; as the database is being used its

performance is noted and if users are not satisfied modifications can be made

to improve performance.

A DBMS has the following languages

Data Definition Language ( DDL )

Data Manipulation Language ( DML )

Structured Query Language ( SQL )

Let us discuss these languages ….

Data Definition Language

The DDL allows users to specify the data types and structures of the

constraints on the data to be stored in the database.

E.G.

Create table Employees (empid INT UNIQUE NOT NULL, empname

varchar (15), empaddress varchar (25), primary key (empid))

Having a central repository for all data and data descriptions allows

the DML to provide a general inquiry facility to this data called a query

language; the most common query language is the Structured Query

Language (SQL)

Now what is SQL?????

Structured Query Language

SQL is a standard language for accessing and manipulating relational

databases

Is a relational database language which contains 3 kinds of statements:

i)Data definition statements that lets a user define and modify the schema of

the database

ii)Data manipulation statements that lets a user create, access and modify

data in the database

iii)Data control language statements such as the GRANT and REVOKE

The SQL standard

 Executes queries to retrieve data

 Insert, update and delete rows in a table

 Create, modify and delete objects

 Control access to the database and objects

DML statements

Statement Description

SELECT Retrieves and displays one or more

rows from a table

INSERT INTO Adds news rows to a table

UPDATE Changes column values in one or more

rows of a table

DELETE FROM Deletes one or more rows from a table

UNION Combines two or more SELECT

statements to create a complex query

THANK YOU

QUESTIONS????