Introduction to SQL and

Conceptual Modelling Concepts

Introduction to SQL
Topics
• Database Languages
• What is SQL?
• Why use SQL?
• Why is SQL important?
• SQL statement construction
 Database Languages
◼ Every database environment requires language commands to do required processing.
◼ Data Definition Language (DDL)
◼ Data Manipulation Language (DML)
◼ Host Language
◼ Query Language
 What is SQL?
• Every DBMS has Data Language embedded in its architecture that is used to communicate with Database.
• Can be classified by type of operation
▪ Data Definition Language (DDL)
• Facilitates Creation and Description of Database.
▪ Data Manipulation Language (DML)
• Deals with Manipulation and Processing of Data.
▪ Data Control Language (DCL)
• Deals with Data Access.
▪ Transaction Control Language (TCL)
• Manages transactions in the database and the changes made to the data in a table by DML statements.
SQL - Category

DDL • CREATE, DROP, ALTER

DML • SELECT, INSERT, UPDATE, DELETE
DCL • GRANT, REVOKE
TCL • COMMIT, ROLLBACK
 What is SQL?
• SQL has been standardized by ANSI and has been adopted as Industry Standard for Relational Database Systems.
• Available in Different Platforms
▪ Oracle : Large Multi-user Client / Server Projects
▪ Microsoft Access :Entry Level DBMS for Small scale Projects
▪ MySQL: Open source SQL database
▪ MS SQL Server :DBMS from Microsoft
 Host Language
• Data are seldom manipulated without some intended purpose.

◼ The language that is used for database

application programming is the host
language for the DBMS.
◼ Used by programmers to embed SQL statements into
their application : COBOL, C, Java
 Why use SQL

• Allow users to:

• Create Database and Relation Structures
• Insert, Modify and Delete Database Data
• Perform simple and complex queries.
• Performs all these tasks with minimal user effort, and command structure / syntax (relatively) simple to learn.
• Portable and Standardized and have DDL and DML component parts to it.
• Declarative: State question to answer not how to answer it
 SQL - Statement Construction
• SQL statements consists of reserved words and user-defined words.
▪ Reserved words are a fixed part of SQL and must be spelt exactly as required and cannot be split across lines.
▪ User-defined words are made up by user and represent names of various database objects such as relations, columns, views.

• Most components of an SQL statement are case insensitive, except for literal character data.
• More readable with indentation and lineation:
▪ Each clause should begin on new line.
▪ Start of clause should line up with start of other clauses.
▪ Where clause has several parts, each should appear on separate line and be indented under start of clause.
Conceptual Modelling Concepts
and their Representation
Topics
• Data Model
• Why Data Model
• ER Model
• Goals of ER Model
• Diagram conventions
 Data Modeling
• Data Model is conceptual representation of Data Structures that
are required by Database.
• Data Model focuses on what data is required and how it should be
organized rather than what operations will be performed on data.
• Data Model is independent of hardware or software constraints.
• Data Model focuses on representing data as user sees it in "real
world”.
Methodology
• Two major Methodologies used to create Data Model
▪ Entity - Relationship (ER) Model
▪ Object Model
 Database Design
◼ Collection of processes that facilitate the designing, development, implementation and maintenance of
enterprise data management systems.

◼ Properly designed database are easy to maintain, improves data consistency and are cost effective in terms of
disk storage space.

◼ The database designer decides how the data elements correlate and what data must be stored.

◼ Main objectives of database design in DBMS are to produce logical and physical designs models of the proposed
database system.
 Database Design Process
◼ Requirements Collection and Analysis : Fact Finding (Interviews, Observation, looking at current data)
◼ Conceptual Database Design: Entities, ER Modelling, Function Specs.
◼ Choice of DBMS
◼ Data Model mapping (Logical Database Design)
◼ ER Model Relations, Normalisation
◼ Physical Database Design: Indexes, Physical Storage etc.
◼ Implementation
 Database Design Process
◼ Requirement analysis
◼ Requirements Collection and Analysis : Fact Finding (Interviews, Observation, looking at current data)

◼ Database designing
◼ Logical model – Concerned with developing a database model based on requirements. The entire design is on paper without
any physical implementations or specific DBMS considerations.
◼ Physical model – This stage implements the logical model of the database taking into account the DBMS and physical
implementation factors.

◼ Implementation
 Database Design Process
[Link] Database Design: Defines WHAT
A schema is a “description of the structure of the database”.
the system contains through data analysis and
requirements gathering
Conceptual Logical Physical [Link] Database Design: Defines HOW the
Database Database Database system should be implemented regardless of the
Fact
Design Design Design
finding DBMS.
results
[Link] Database Design: Describes HOW the
Conceptual Logical Physical system will be implemented using a specific
schema schema schema DBMS system and is created by DBA and
developers. The purpose is actual implementation
High level and of the database.
independent from Can be processed Describes storage
any DBMS by the DBMS structures
Database – Levels of Abstraction
• Database systems comprise complex data-
structures.

Logical Level

Physical Level
External Level
• Abstraction creates a general idea of what the • End users • What • How the
problem is and how to solve it. work on information data is
• To make the system efficient in terms of data view level or data is stored
retrieval, and reduce complexity, developers use • Most used stored in physically
for VIEW the and where
abstraction.
purpose database is it stored
• Abstraction hide irrelevant details from the users in database
and approach simplifies database design.
Abstraction – Walk Through

Users see the data in the form of rows and columns

Describes the actual data stored in the database in

the form of tables and relates them by means of
mapping

In most of the cases this level is not mentioned and

usually it is said that we have 3 levels of data
abstraction

Describes how the data is stored in the physical

memory like magnetic tapes, hard disks etc
Key Message
Application Users are examples of External
Level.
Users Need not Necessarily have elaborated
knowledge on Database

Physical Level of Abstraction is created by

Database Administrators
Questions ?

If you are using Google search,

which Level of Abstraction are you working in ?

EXTERNAL
Database Schema
• Conceptual Schema: E-R models
• Internal Schema : Logical schema and Physical schema
• External Schema
▪ User Views
▪ Subsets of Conceptual Schema
▪ Can be determined from business-function/data entity matrices
▪ DBA determines schema for different users
 Data Modeling
• Data Model is one part of Conceptual Design Process
• Data Model focuses on what data should be stored in Database while Functional Model deals with how Data is
processed.
• Data Model is used to design Relational Tables.
• Functional Model is used to design queries, which will access and perform operations on those tables.
 Components of Data Model
• Data Model gets its inputs from Planning and Analysis Stage.
▪ Information collects during requirements analysis such as interviewing end - users.
▪ Information collects by reviewing existing documentation.
• Data Model has two outputs. Entity - Relationship Diagram and Data Document
• Entity - Relationship Diagram represents data structures in pictorial form.
• Data Document describes in detail Data Objects, Relationships, and rules required by Database.
• Dictionary provides the detail required by Database Developer to construct Physical Database.
 Why is Data Modeling?
• Goal of Data Model is to make sure that all Data Objects required by Database are completely and accurately
represented.
• Data Model is also detailed enough to be used by Database Developers to use as "blueprint" for building Physical
Database.
• Information contained in Data Model will be used to define Relational Tables, Primary and Foreign Keys, Stored
Procedures, and Triggers.
 Why Conceptual Design
◼ Independence from particular DBMS ◼ Part of whole Database specification
◼ Choice of target DBMS can be postponed ◼ Easier to make amendments later
◼ Change of requirements ◼ It describes exactly Information needs of business.
◼ Use of federated systems ◼ It facilitates discussion
◼ Ease of use ◼ It helps to prevent mistakes, misunderstanding.
◼ Technical database knowledge not prerequisite ◼ It forms Important "ideal system" documentation.
◼ User involvement ◼ It forms sound basis for Physical Database Design.
 Entity Relationship Modeling
• Entity Relationship (ER) Modeling is about modeling business based on current or desired functionality of future
system.
• High Level Data Model that is useful in developing Conceptual Model for Database.
• It is well-established technique that leads to diagrams which are quite easy to read and therefore also easy to check.
 Goals of ER Modeling
• All Information that is required to run business properly are recognized.
• Model should be complete. Requirements should be known before implementing.
• Dependencies must be clear.
• Every single piece of required information appears only once in the model.
▪ This implies that ideal system does not contain derivable information.
• Information available in predictable, logical place.
 Goals of ER Modeling
• Related information is kept together.
• Proper Entity Relationship model leads to set of logically coherent tables.
• ER Model can easily be transformed into Relational Tables.
• It is simple and easy to understand. Model can be used by Database Designer to communicate design to end user.
• Model can be used as design plan by Database Developer to implement a Data Model in specific DBMS.
Identify Entities and Attributes

Entities

Attributes
 Entity
• Entity is any type of object or event, real or abstract that we wish to store data
• Entity is one member or instance of Entity Type.
▪ Entity is abbreviation of Entity Type.
▪ It represent set of Entity instances which can be described by same set of Attribute types.
 Entity
• Value of same attribute for each entity instance may be different.
• Name of each entity is in singular form
▪ Noun
▪ Adjective + Noun
▪ Noun + Noun => (noun string)
▪ Adjective + Noun + Noun
• Examples: Customer, Customer Order, Product, Hourly Employee, Project, Department, Unfilled Customer Order
 Entity
Entity
• Be clear and concise and avoid abbreviation
• Be consist with user’s terminology Employee Book Product
• Identify synonyms
▪ Customer Client
▪ Product Merchandise
Customer
▪ Supplier Vendor Customer Department
Order
▪ Teacher Faculty
▪ Use one name as the official name and document others as aliases
 Where to Look for Entity?
• Tangible or Intangible Things
▪ Nouns that are used to describe problem domain will often correspond to major Entity of the system, at least at a high
level. Examples: Product , Sensor, Employee, Department, Sale Office
• Resources
▪ Any resources that an organization needs to manage should be represented as an Entity. Information assists the efficient
and effective use of other resources through improved decision.
▪ Examples: Inventory, Machine, Bank Account, Customer
 Where to Look for Entity?
• Roles Played
▪ Roles can be played by persons or organizational units.
▪ Examples: Customers, Managers, Account representatives
• Events
▪Events are incidents that occur at points in time.
▪Event often involved interaction between two Entity or action that changes status of Entity.
▪Examples: Sale, Delivery, Registration of a motor vehicle
 Criteria for Evaluating Entity
• Exist more than one Entity occurrence (instance) in Entity.
❑ Entity that will be composed of multiple Attributes
❑ Entity that we are trying to model
• Have at least Unique Identifier.
• Domain-based requirements
❑System must have in order to operate.
❑These may be clearly specified in problem description or known from subject matter experts.
 Entities Classified
• Entities are classified as
▪ Independent
▪ Dependent
• Independent Entity is one that does not rely on another for identification.
• Dependent entity is one that relies on another for identification.
• Entity Occurrence (also called instance) is individual occurrence of Entity.
 Special Entity Types
• Associative Entities (also known as Intersection Entities) are entities used to
associate two or more entities in order to reconcile Many – To – Many
relationship. (they do not have primary keys)
• Subtypes Entities are used in generalization hierarchies to represent subset
of instances of their parent entity, called Supertype, but which have
attributes or relationships that apply only to subset.
 Attribute
Attribute
• Attribute serves to qualify or quantify entity • Examples
• Property or Characteristic of entity type that is of interest to ▪ Customer has customer name, address, and
organization telephone number
• Naming Conventions: ▪ Product has quantity-on-hand, weight,
▪ Names that have business meaning volume, color, and name.
▪ Don't use abbreviation or possessive case, e.g., PN and ▪ Employee has SSN, salary, and birthday.
Customer's name ▪ Employee-works-for-project has percentage-
• Set of all possible values of attribute is Attribute Domain. of-time, starting-date.
 Attribute
• Entity must have Unique Identifier
• Unique Identifier can be of single or combination of Attributes
• Candidate Key is Attribute or combination of Attributes that uniquely identifies each instance of Entity.
• Primary Key is Candidate Key that has been selected as Unique identifier for Entity, all remaining Candidate Key are
called Alternate Key
• Attributes that are not Primary Key, Candidate Key or Alternate Key are called NonKeys
• Attributes have values. Attribute value can be number, character string, date, image, sound, and even more.
• Sometimes value of Attribute is unknown or missing, and sometimes value is not applicable.
 Attribute Category
• Basic • Derived • Designed
▪ Attribute Value that cannot ▪ Attribute Value can be calculated or ▪ Attribute is created to overcome
deduced from relationship Groupings or
be deduced or calculated. system constraints (primary key and
from values of other Attributes.
▪ Examples foreign key).
▪ Value of Derived Attribute changes
❖Student name constantly. ▪ Value of a Designed Attribute does
❖College ▪ Examples not change.
❖Student Age ▪ Examples
❖Account Balance ❖Student ID
❖Number of courses taken.
❖Course number.
 Attribute Types
• Attribute can be simple or composite.
• Simple Attribute, such as grade, is one component that is atomic.
• Composite Attribute, such as "John Vagas", has multiple components, such as "John" and "Vagas"; and each
component is atomic or composite.
• Another way to classify attributes is either as single-valued or multi-valued.
• Single Valued Attribute usually holds exactly one value such as, emp no: 5674993.
• Multi Valued Attribute has more than one value for a particular Entity such as, skill attribute of the employee.
Attributes Designed Derived
Attribute Attribute Attribute

Attribute Attribute
Multi Valued
Attribute
Composite
Attribute
Attribute
Entity with Attributes
• Example of Entity with Attributes
Entity Attribute

• Examples of Entity with Attributes

Employee
Employee number
First name
Last name
Employee function
Attribute_List
Employee salary
Bank Account (Account_No, Account_Holder_Name,
Bank_Branch, Address, Balance)
Any Questions?