L03 – DDL, DML,
Database Design
Data Definition Language (DDL)
• Specification notation for defining the database schema
Example: create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
• DDL compiler generates a set of table templates stored in a data dictionary
• Data dictionary contains metadata (i.e., data about data)
• Database schema
• Integrity constraints
• Primary key (ID uniquely identifies instructors)
• Authorization
• Who can access what
15-Aug-22 Database Design 2
Data Definition Language
• Here are some tasks that come under DDL:
• Create: It is used to create objects in the database.
• Alter: It is used to alter the structure of the database.
• Drop: It is used to delete objects from the database.
• Truncate: It is used to remove all records from a table.
• Rename: It is used to rename an object.
• Comment: It is used to comment on the data dictionary.
15-Aug-22 Database Design 3
Data Manipulation Language (DML)
• Language for accessing and updating the data organized by the
appropriate data model
• DML also known as query language
• There are basically two types of data-manipulation language
• Procedural DML -- require a user to specify what data are needed and how to
get those data.
• Declarative DML -- require a user to specify what data are needed without
specifying how to get those data.
• Declarative DMLs are usually easier to learn and use than are
procedural DMLs.
• Declarative DMLs are also referred to as non-procedural DMLs
• The portion of a DML that involves information retrieval is called a query
language.
15-Aug-22 Database Design 4
Data Manipulation Language
• Here are some tasks that come under DML:
• Select: It is used to retrieve data from a database.
• Insert: It is used to insert data into a table.
• Update: It is used to update existing data within a table.
• Delete: It is used to delete all records from a table.
• Merge: It performs UPSERT operation, i.e., insert or update operations.
• Call: It is used to call a structured query language or a Java subprogram.
• Explain Plan: It has the parameter of explaining data.
• Lock Table: It controls concurrency.
15-Aug-22 Database Design 5
SQL Query Language
• SQL query language is nonprocedural. A query takes as input several tables
(possibly only one) and always returns a single table.
• Example to find all instructors in Comp. Sci. dept
select name
from instructor
where dept_name = 'Comp. Sci.'
• SQL is NOT a Turing machine equivalent language
• To be able to compute complex functions SQL is usually embedded in some higher-
level language
• Application programs generally access databases through one of
• Language extensions to allow embedded SQL
• Application program interface (e.g., ODBC/JDBC) which allow SQL queries to
be sent to a database
15-Aug-22 Database Design 6
Data Control Language
• The DCL execution is transactional. It also has rollback parameters.
• Here are some tasks that come under DCL:
• Grant: It is used to give user access privileges to a database.
• Revoke: It is used to take back permissions from the user.
• There are the following operations which have the authorization of
Revoke:
• CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and
SELECT.
15-Aug-22 Database Design 7
Transaction Control Language
• TCL is used to run the changes made by the DML statement.
• TCL can be grouped into a logical transaction.
• Here are some tasks that come under TCL:
• Commit: It is used to save the transaction on the database.
• Rollback: It is used to restore the database to original since the last
Commit.
15-Aug-22 Database Design 8
Database Access from Application Program
• Non-procedural query languages such as SQL are not as powerful as a
universal Turing machine.
• SQL does not support actions such as input from users, output to
displays, or communication over the network.
• Such computations and actions must be written in a host language,
such as C/C++, Java or Python, with embedded SQL queries that access
the data in the database.
• Application programs -- are programs that are used to interact with the
database in this fashion.
15-Aug-22 Database Design 9
Database Design
• The process of designing the general structure of the database:
• Logical Design – Deciding on the database schema. Database design
requires that we find a “good” collection of relation schemas.
• Business decision – What attributes should we record in the database?
• Computer Science decision – What relation schemas should we have and how
should the attributes be distributed among the various relation schemas?
• Physical Design – Deciding on the physical layout of the database
15-Aug-22 Database Design 10
15-Aug-22 Database Design 11
15-Aug-22 Database Design 12
What is Database Design?
• Collection of tasks or processes that enhance the designing,
development, implementation, and maintenance of enterprise data
management system.
• Designing a proper database reduces the maintenance cost thereby
improving data consistency and the cost-effective measures are
greatly influenced in terms of disk storage space.
• The designer should follow the constraints and decide how the
elements correlate and what kind of data must be stored.
15-Aug-22 Database Design 13
What is Database Design?
• The main objectives behind database designing are to produce
physical and logical design models of the proposed database system.
• To elaborate this, the logical model is primarily concentrated on the
requirements of data and the considerations must be made in terms
of monolithic considerations and hence the stored physical data must
be stored independent of the physical conditions.
• On the other hand, the physical database design model includes a
translation of the logical design model of the database by keep control
of physical media using hardware resources and software systems
such as Database Management System (DBMS).
15-Aug-22 Database Design 14
Why is Database Design important?
• Database designs provide the blueprints of how the data is going to be stored
in a system.
• A proper design of a database highly affects the overall performance of any
application.
• The designing principles defined for a database give a clear idea of the
behavior of any application and how the requests are processed.
• Another instance to emphasize the database design is that a proper database
design meets all the requirements of users.
• Lastly, the processing time of an application is greatly reduced if the
constraints of designing a highly efficient database are properly
implemented.
15-Aug-22 Database Design 15
Life Cycle
15-Aug-22 Database Design 16
Phases of database design
Logical Physical
• All the entities and relationships amongst • Convert the entities into tables.
them.
• Convert the defined relationships into
• Each entity has well-specified attributes. foreign keys.
• The primary key for each entity is • Convert the data attributes into columns.
specified.
• Modify the data model constraints based
• Foreign keys which are used to identify a on physical requirements.
relationship between different entities are
specified.
• Normalization occurs at this level.
15-Aug-22 Database Design 17
15-Aug-22 Database Design 18
