Database Design Methodology

1
Database Design Methodology

• Lector Diana Leskovska
• Prepared for Training Program in
• Netage Solutions Inc.
• With permissions to use in the course
• Modern Software Technologies
• Faculty of Mathematics and Informatics
• Sofia University

2
Introduction

• The subject mater is Database
• What you will learn

3
Introduction to Database

• What is a Database?
• Relational Databases and Database Management
  Systems (DBMS)
• History

4
What Is a Database?

• Collection of data
• Assessed
• Managed
• Updated
• Relational Database
• Distributed Database
• Object-oriented programming database
• Structured Query Language (SQL)

5
What is a Database?

• Computer-based repository for data
• Distinctive characteristics
• Large amount of data (auxiliary memory)
• Complex data organizations
• Centered around data, not processes
• Model real-world enterprises

6
Advantages of Databases

• Data sharing
• Privacy (authorization mechanism)
• Decreased redundancy and increased consistency
• Reliable storage of data (backup and recovery
  mechanisms)
• Enforcement of standards

7
Relational Databases and Database Management
Systems (DBMS)

• A DBMS is a computerized record-keeping system
  that stores, maintains and provides access to
  information. A database system involves four
  major components
• Data
• Hardware
• Software
• Users

8
The three level architecture of DBMS
9
Internal Level of DBMS
10
External Level of DBMS
11
Conceptual Level of DBMS
12
Advantages of using DBMS

• Clear picture of logical organization of data set
  
• Centralization for multi-users.
• Data independence

13
History

• Integrated Data Store, first general-purpose
  DBMS, early 1960s, Charles Bachman, General
  Electrics
• Information Management System (IMS), late 1960s,
  IBM
• Relational database model, proposed in 1970,
  Edgar Codd, IBMs San Jose Laboratory
• Structured Query Language (SQL), standardized in
  the late 1980s
• More powerful query languages, complex analysis
  of data, support for new data types (e.g.,
  images), late 1980s and 1990s
• Packages which come with powerful customizable
  application layers
• Internet

14
Data Models

• Hierarchical
• Network
• Relational
• Object
• Object/Relational
• Associative

15
Relational Database Fundamentals

• Data Models
• Relational Model Terminology
• Using Tables to Represent Data
• Entities and Data Relationships
• Integrity
• Database Languages
• Data Dictionaries
• Multitier Database Architecture

16
Data Models (1)

• What is Data Model?
• A graphical and textual representation of
  analysis that identifies the data needed by an
  organization to achieve its mission, functions,
  goals, objectives, and strategies and to manage
  and rate the organization. A data model
  identifies the entities, domains(attributes), and
  relationships (or associations) with other data,
  and provides the conceptual view of the data and
  the relationships among data.

17
Data Models (2)

• Grammar
• Vocabulary
• Content

18
Data Models (3)

• Relational Model
• The Client/Server Model
• File-Server vs. Distributed Client/Server
• The Entity-Relationships Model

19
Relational Model

• Data values carry relationships
• Advantage flexibility
• Tables
• Views
• Joins

20
The Client/Server Model

• Single-user client machine
• Multi-user server machine
• Sharing processes load between the two
• The client request services
• The server provides services

21
File-Server vs. Distributed Client/Server

• Microsoft Access Database (MDB) supports
  multi-user access, but
• It is not true client/server implementation
• It it file-server implementation

22
A Typical File-Server Configuration
Database Engine
Database Engine
Database Engine
Network File Server
Database
23
A Typical Client/Server Configuration
Database Engine
Database Engine
Database Engine
Network Server
Database Server
Database
24
Relational Model

• Data structure
• Data manipulation
• Data integrity

25
Relational Model

• Data Modeling Concepts
• Entities
• Attributes
• Relationships
• SQL
• Tables Student, Course
• Columns in tables Students Name, Course Number
• Primary/Foreign Key columns or tables Teacher
  to Class

26
Case Study

• A College Enrolment DataBase
• Teachers professors and teaching assistants
  100 and 200
• Courses - 400
• Students 4 000
• Class a course taught by one teacher to many
  students in particular time of the week

27
Using Tables to Represent Data
28
Special Properties of Relational Tables

• Entries in columns are single-valued
• Entries in the columns are of the same kind
• Each row is unique
• Sequence of columns (left to right) is
  insignificant
• Sequence of rows (top to bottom) is insignificant
• Each column has a unique name

29
Entities and Data Relationships

• Entity
• Attributes
• Relationships
• Entity instances

30
Data Model Diagram Entity/Relationships Diagram
Entities
COURSE defines a subject material taught in a
CLASS
TEACHER A person employed by the collegewho is
responsible for instructing a CLASS to STUDENTS
CLASS SESSION is an instance of a CLASS that
occurs at a particular date and time
STUDENT A person who is enrolled in CLASSES at
the college and attend CLASS SESSIONS
CLASS Scheduled instance of COURSE that is
taught by one TEACHER and that meets in a
particular room during specific times of the week
31
Data Model Diagram Entity/Relationships Diagram
Attributes
32
Data Model Diagram Entity/Relationships Diagram
Relationships
STUDENT A person who is enrolled in CLASSES at
the college and attend CLASS SESSIONS
TEACHER A person employed by the collegewho is
responsible for instructing a CLASS to STUDENTS
CLASS Scheduled instance of COURSE that is
taught by one TEACHER and that meets in a
particular room during specific times of the week
CLASS SESSION is an instance of a CLASS that
occurs at a particular date and time
COURSE defines a subject material taught in a
CLASS
33
Entity/Relationships Diagram
mentors teacher_id mentor_id
students id
enrollment student_id classsessions_id
classsessions id (PK) classes_id (FK) room_id
(FK) begin_time end_time
teachers id (PK)
rooms id (PK)
classes id teacher_id course_id
departments id
courses id dep_id
34
Tables

• Entities are modeled as tables
• In a table, each instance of an entity is called
  a row (record)
• Attributes are modeled as columns in a table
  (fields)

35
Table Teacher
36
Choosing and Defining Primary Key

• Primary Key is a special column or group of
  columns for identifying any row in a table
• Value of Primary Key must be unique
• Two way of choosing
• Choose existing column
• Create new column for the purpose of Primary
  KeyIdentity auto-number - sequence

37
Table Teacher with Primary Key
38
Columns Constraints

• Identify business rules that relate to data
  integrity
• Incorporate business rules and constraints to
  data model
• Not null (NN)
• No duplicates (ND)
• No changes (NC)
• Defaults

39
Table Teacher with Constraints
40
Implementing Relationship

• Primary Key
• Foreign Key

41
Implementing Relationship
instructs
defines subject taught
42
(No Transcript)
43
Modeling Relationships

• One to One

44
One to One Relationship

• Only one matching record
• Uses primary keys for both tables
• Use to limit access to information

45
Modeling Relationships

• One to One
• One to Many

46
One to Many Relationship

• Most common type of relationship
• Related between primary and foreign keys
• Can have many related records
• Referential integrity prevents orphaned records

47
Modeling Relationships

• One to One
• One to Many
• Many to Many

48
Many to Many Relationship

• One order, many products
• One product, many orders
• Not directly supported between tables
• Use an associative entity or join table

49
(No Transcript)
50
Integrity(??????????)

• Resistance to alteration by system errors,
  preserves consistency and correctness of data
• Entity integrity
• Domain integrity
• Referential integrity

51
Referential Integrity

• Rules to preserve relationships
• Prevents orphan records
• Cannot add records on many sides
• Cannot delete from one side
• Cascade update
• Cascade delete

52
Normalizing

• Dependencies between data are identified
• Redundant data is minimized
• Reduces chances of data errors
• Reduces disk space
• The data model is flexible and easier to maintain

53
Normal Forms

• First Normal Form
• Eliminate repeating attributes
• Second Normal Form
• Eliminate partial key dependencies
• Third Normal Form
• Eliminate dependencies between non-key attributes

54
Impact of Normalization

• Is normalization appropriate?
• Performance, maintainability, extensibility,
  scalability, availability, security

55
Components in a DBMS

• Databases
• Tables
• Columns
• Rows
• In some
• Views
• Indexes
• Triggers
• Stored Procedures
• User connections
• Locks

56
Types of Data in Database

• Number (special Autonumber)
• Text
• Date/Time
• Currency

57
Database Languages

• Database Definition Language
• Database Manipulating Language

58
Data Dictionary

• Collection of descriptions of the data objects or
  items

59
Simple Two Tier Architecture
60
Complex Two Tier Architecture
61
Simple Three Tier Architecture
62
Complex Three Tier Architecture
63
Multitier Database Architecture
64
Database Planning

• The Database System Life Cycle
• The Database Requirements Document
• Selecting a DBMS
• Selecting an Application Interface
• Case Study

65
Database System Life Cycle

• Needs assessment
• Database design
• Application Development
• Performance Tuning
• Enhancements

66
Needs Assessment

• Who will use the application?
• What use will the application fill in the
  organization?
• How do people plan on using the application?

67
Database Design

• Creating and entity-relationship diagram (ERD)
• Translating an ERD into logical data model

68
Application Development

• Integrity constants, triggers
• Stored procedures and/or packages
• SQL statements
• Stepwise refinement process
• Complexity of application

69
Performance Tuning

• No application is harder to use than a slow one

70
Database Security

• Handle large user populations
• Limit access
• Levels of data access
• Privileges

71
Enhancements

• Reduced features in version 1
• Evolution of business process that must be
  modeled by the application

72
Database System Life Cycle
73
Database Planning

• The Database System Life Cycle
• The Database Requirements Document
• Selecting a DBMS
• Selecting an Application Interface
• Case Study

74
Selecting DBMS

• Microsoft SQL Server (popularity maintained by
  Microsoft's overall market power)
• Oracle (you won't get fired)
• Informix
• PostgreSQL (free open-source)
• InterBase (free open-source)
• mySQL

75
Choosing an RDBMS vendor

• Cost/complexity to administer
• Lock management system
• Full-text indexing option
• Ease of running standard programming languages
  internally
• Support

76
Selecting an Application Interface

• OLE DB
• ODBC (Open Database Connectivity)
• CORBA (Common Object Request Broker Architecture)
• DCOM (Microsofts Distributed Component Object
  Model) - ADO
• DCE (Distributed Computing Environment)
• JDBC (Java Database Connectivity)

77
Overview of Database Design Methodology

• The effects of Poor Database Design Practices
• The Phases of Database Design
• Conceptual Database Design
• Entity-Relationship Models
• Normalization

78
Database Design

• Objectives
• Group data into entities by applying
  normalization rules
• Identifying primary keys
• Choose a foreign key that will enforce a
  relationship between entities and ensure
  referential integrity
• Identify the business rules that relate to data
  integrity

• Incorporate the business rules and constraints
  into the data model
• In a given situation decide whether
  denormalization is appropriate
• Assess the potential impact of the logical design
  on performance, extensibility, maintainability,
  scalability, availability and security

79
Design Process
80
Design Models

• Conceptual Design
• Logical Design
• Physical Design

81
Output of the Three Independent Design Process
Models
Conceptual Design
Logical Design
Scenarios
Physical Design
Services and Objects, User Interface, and
Logical Database
Components, User Interface, and Physical Database
82
Characteristics of the Design Models

• Overlapping
• Iterative
• Spiral

83
Conceptual Design

• A design based on real data from the customer and
  users
• A coherent, integrated picture of the product
• Useful levels of abstraction or classification
• A common set of expectations among the customer,
  users and project team
• Group consensus in design
• Synchronization with the enterprise architecture
• A basis for team communication

84
Logical Design (1)

• Manages and reduces complexity by defining the
  structure of the solutiondescribing the parts
  of the system, and how the parts interact to
  solve the problem
• Sets boundaries and describes interfaces to
  provide structure for interaction
• Uncovers any errors and inconsistencies in
  Conceptual Design

85
Logical Design (2)

• Eliminates redundancy and identify potential
  reuse
• Provides foundation for Physical Design
• Improves the operation between the various parts
  of the system
• Common view of the solution among project team
  members

86
Physical Design

• Break down the system requirements to simplify
  the segmenting and estimating the work needed to
  create the systemprovide focus and clarity for
  developers
• Provides a bridge between Logical Design and
  implementation
• Evaluates implementation options
• Provides a flexible design based on services
• Seeks compatibility with the organizations
  architecture
• Traces back use cases and scenarios through
  Logical Design

87
Logical Database Design

• Logical Database Design
• Creating a Logical Data Model
• Using a Database Definition Language
• Validating the Logical Data Model
• Defining Integrity constraints
• Creating an Enterprise Data Model

88
Physical Database Design

• Physical Database Design
• Creating Enterprise Constants
• Using Secondary Indexes
• Denormalization
• Creating User Views
• Designing Database Access Rules

89
Steps in Logical Database Design
90
Steps in Physical Database Design
91
Relational Algebra

• Introduction to Relational Algebra
• 8 Relational Operators

• Selection
• Project
• Product
• Join

• Union
• Intersection
• Difference
• Division

92
Selection (restriction)

• Retrieves a subset of rows from a relational
  table bases on value(s) in a column or columns
• ?(Selection Condition)(R)
• Clauses
• of the operations , lt, lt, gt, gt, lt gt, !
  on attributes
• Boolean operations
• AND, OR, NOT

93
Selection (restriction)
Teachers
?(Last_Name gt T AND Salary gt 20)(Teachers)
94
Project

• Retrieves a subset of columns from a relational
  table, removing duplicates from the result
• (attribute list) (R)
• (Last_Name, Salary) (Teachers)

95
Product

• One row from starting table is concatenated with
  every row in the other row
• Each and every row in the first table
• Cartesian Product
• Cross Join
• Cross Product
• R x S

96
Product
Teachers
Courses
97
Product
Teachers x Courses
98
Product

• Interim result when performing a join
• You have to apply special criteria to include
  only those rows that are related in some
  meaningful way

99
Join

• Horizontally combines (concatenates) rows in one
  table with rows in another or the same table,
  including only rows which meet some selection
  criteria relating columns in two tables

100
Join

• Combines product and selection operators
• Concatenates data from one row of a table with
  rows in another or the same table,
• When certain criteria are met
• R (join criteria) S

101
Join

• Teachers (Teacher.ID Courses.Teacher_ID)
  Courses
• Join based on equality is called equi-join
• If redundant columns are removed from the result
  natural join
• Inner join when includes only matched rows

102
Equi-Join
103
Natural Join
R S
104
Outer Join

• Includes matched rows and unmatched rows in the
  result
• Null value for columns that inapplicable
• One-way outer join includes the unmatched rows
  only from one table left outer join or right
  outer join
• Two-way outer join includes unmatched rows from
  both tables full outer join

105
Full Outer Join
Teachers (Teacher.ID Courses.Teacher_ID)
Courses
106
Union

• Vertically combines (stacks) rows of one table
  with rows in the same or another table, removing
  duplicates
• R ? S
• Tables must have the same number and type of
  columns

107
Group 1
Group 1 ? Group 2
Group 2
108
Outer Union

• Without restriction for the same shape
• Outer union has the same effect as outer natural
  join
• One-way outer union
• Two-way outer union

109
Two-way Outer Group 1 ? Group 2
Group 1
Group 2
110
Intersection

• Results in rows common to two (or more)
  relational tables
• R ? S

111
Difference

• Results in rows that appear in one table and not
  in another
• R S

112
Math_club
Ski_club
Intersection of tablesMath_club ? Ski_club
Difference of tables Math_club Ski_club
113
Division

• Results in column values in one table for which
  there are other matching column values
  corresponding to every row in another table
• R?S

114
Division
R
S1
R?S1
S2
R?S2
115
Division
Completed_Course (dividend)
Division_Result (quotient)
Relational_Major (divisor)
116
Structured Query Language

• Introduction to SQL
• Data Definition Language
• Data Manipulation Language
• Data Control Language

117
Introduction to SQL

• SQL database language
• Basically SQL consists of the following statement
  types
• Data definition language
• Data manipulation language
• Others like security, etc
• In addition SQL can be used as an interactive
  query language or it can be embedded within a
  programming language (like C).

118
Data Definition Language

• The DDL in a database query language is the data
  definition sublanguage, and is used to define the
  structure of data in the database. The other
  component of a database query language is the
  DML. Relational databases with an SQL interface
  have a number of SQL Data Definition Language
  Statements.

119
SQL DDL

• The SQL data definition language (DDL) statements
  operate on base tables. The principal DDL
  statements are as follows
• CREATE TABLE
• DROP TABLE
• ALTER TABLE
• CREATE INDEX
• DROP INDEX

120
Data Manipulation Language

• The DML in a database query language is the data
  manipulation component, and is used to create,
  delete, update, and retrieve data from the
  database. The other component of a database query
  language is the DDL. Relational databases with an
  SQL interface have a number of SQL Data
  Manipulation Language Statements.

121
SQL DML

• SQL data manipulation
• SQL provides four data manipulation language
  (DML) statements. These are
• SELECT
• UPDATE
• DELETE
• INSERT
• The tables manipulated by the DML statements can
  in general be base tables or views.

122
SQL Standardization
123
Select

• SELECT statement retrieves rows from the
  database
• SELECT ALL DISTINCT ltselect_listgt INTO
  ltnew_table_namegt
• FROM lttable_namegt , lttable_name2gt ...,
  lttable_name16gt
• where lttable_namegt ltview_namegt
• ltdatabasegt.ltownergt.lttable_namegt.
  ltview_namegt.

124

• WHERE ltclausegt
• GROUP BY ltclausegt
• HAVING ltclausegt
• ORDER BY ltclausegt
• COMPUTE ltclausegt
• FOR BROWSE

125

• ltjoined_tablegt lttable_namegt CROSS JOIN
  lttable_namegt lttable_namegt INNER LEFT OUTER
  RIGHT OUTER FULL OUTER JOIN
  lttable_namegt ON ltsearch_conditionsgt
  ltoptimizer_hintsgt

126

• One or more of the following, separated with a
  space
• INDEX ltindex_namegt ltindex_idgt
• NOLOCK HOLDLOCK UPDLOCK TABLOCK
  PAGLOCK TABLOCKX FASTFIRSTROW

127

• WHERE ltclausegt WHERE ltsearch_conditionsgt
• GROUP BY ltclausegt
• GROUP BY ALL ltaggregate_free_expressiongt
  , ltaggregate_free_expressiongt...
• WITH CUBE ROLLUP
• HAVING ltclausegt HAVING ltsearch_conditionsgt

128

• ORDER BY ltclausegt
• ORDER BY lttable_namegt. ltview_namegt.ltcolumn_na
  megt ltselect_list_numbergt ltexpressiongt ASC
  DESC ...lttable_name16gt. ltview_name16gt.ltcol
  umn_namegt
• ltselect_list_numbergt
  ltexpressiongt ASC DESC
• COMPUTE ltclausegt
• COMPUTE ltrow_aggregategt(ltcolumn_namegt) ,
  ltrow_aggregategt(ltcolumn_namegt)...
• BY ltcolumn_namegt , ltcolumn_namegt...

129
Case Study
130
Relational Algebra Operators with SQL

• Project
• SELECT Name, Phone
• FROM Customer

131
Relational Algebra Operators with SQL

• Selection
• SELECT Name
• FROM Customer
• WHERE (Balance gt 500)

132

• We want to select all customers names who work
  in CA, MI, KS, and UT
• SELECT Name
• FROM Customer
• WHERE (State 'CA') OR
• (State 'MI') OR
• (State 'KS') OR
• (State 'UT')

OR is slower than IN
SELECT Name FROM Customer WHERE (State IN ('CA',
'MI', 'KS', 'UT'))
133

• We want to select all customers names balance
  bigger than 450 and smaller than 1000.
• SELECT Name
• FROM Customer
• WHERE ((Balance gt450)
• AND (Balance lt1000))
• SELECT Name
• FROM Customer
• WHERE Balance BETWEEN 450 AND 1000

gt AND lt slower than BETWEEN
134

• We want to select all customers names balance
  which starts with A.
• SELECT Name
• FROM Customer
• WHERE (Name LIKE A)
• _

135

• We want to retrieve a list of orders where
  customer name is Pencho
• SELECT OrderCust.OrderNum
• FROM OrderCust, Customer
• WHERE OrderCust.CustNum
  Customer.CustNum
• AND Customer.Name Pencho

136

• We want to retrieve customer names and their
  balances, where balance is bigger than balance of
  customer 1 (CustNum 1)
• SELECT X.Name, X.Balance
• FROM Customer as X, Customer as Y
• WHERE X.Balance gt Y.Balance
• AND Y.CustNum 1
• SELECT X.Name, X.Balance
• FROM Customer X INNER JOIN
• Customer Y ON X.Balance gt Y.Balance
• WHERE (Y.CustNum 1)

137

• We want to retrieve customer names which did not
  make an order.
• SELECT Name
• FROM Customer
• WHERE CustNum NOT IN
• (SELECT DISTINCT CustNum
• FROM OrderCust)

138

• We want to retrieve list of customer names,
  their balance, which balance is bigger that
  maximal balance of customers from USA.
• SELECT Name, Balance
• FROM Customer
• WHERE Balance gt
• (SELECT max(Balance)
• FROM Customer
• WHERE Country USA
• )

139

• Other realization
• SELECT Name, Balance
• FROM Customer
• WHERE Balance gt ALL
• (SELECT Balance
• FROM Customer
• WHERE Country USA
• )

140

• We want to retrieve customer numbers and names
  which order item number 1.
• SELECT CustNum, Name
• FROM Customer
• WHERE CustNum IN
• (SELECT CustNum
• FROM OrderCust
• WHERE OrderNum IN
• (SELECT OrderNum
• FROM OrderLine
• WHERE ItemNUm 1
• )
• )

141
We want to retrieve dates for orders for item
with namecomputer

• SELECT OrderDate
• FROM OrderCust, OrderLine, Item
• WHERE OrderLine.ItemNum Item.ItemNum
• AND OrderCust.OrderNum OrderLine.OrderNum
• AND ItemName computer

142
SELECT OrderCust.OrderDate FROM Item INNER JOIN
OrderLine ON Item.ItemNum OrderLine.ItemNum
INNER JOIN OrderCust ON OrderLine.OrderNum
OrderCust.OrderNum WHERE (Item.ItemName
'computer')
143
Retrieve customer name, its credit limit, order
number and names of items for each ordered item,
where price is lt 2/3 from customers credit
limit.
SELECT Customer.Name, CreditLimit,
OrderCust.OrderNum, ItemName FROM Customer,
OrderCust, OrderLine, Item WHERE Item.PriceQty
gt 2Customer.CreditLimit/3 AND
OrderCust.OrderNum OrderLine.OrderNum AND
OrderLine.ItemNum Item.ItemNum AND
Customer.CustNum OrderCust.CustNum
144
SELECT Customer.Name, Customer.CreditLimit,
OrderCust.OrderNum, Item.ItemName FROM OrderCust
INNER JOIN OrderLine ON OrderCust.OrderNum
OrderLine.OrderNum INNER JOIN Customer INNER
JOIN Item ON 2 Customer.CreditLimit / 3 lt
Item.Price OrderLine.Qty ON
OrderLine.ItemNum Item.ItemNum AND
OrderCust.CustNum Customer.CustNum
145
Retrieve name and balance of customer with the
highest balance.
SELECT Name, Balance FROM Customer WHERE Balance
(SELECT MAX(Balance) FROM Customer)
146
Retrieve list of name and balance of customers
which balance is not less than the third of
biggestbalances from each customers.
SELECT Name, Balance FROM Customer WHERE Balance
gt (SELECT MAX(Balance) FROM
Customer WHERE Balance lt
(SELECT MAX(Balance) FROM Customer
WHERE Balance lt
(SELECT MAX(Balance) FROM
Customer))) ORDER BY Balance
147
Retrieve customer name and order dates, where
date is the earliest date.
SELECT Name, OrderDate FROM Customer,
OrderCust WHERE OrderCust.CustNum
Customer.CustNum AND OrderDate ( SELECT
MIN(OrderDate) FROM OrderCust )
148
SELECT Customer.Name, OrderCust.OrderDate FROM
Customer INNER JOIN OrderCust ON
Customer.CustNum OrderCust.CustNum WHERE
(OrderCust.OrderDate (SELECT
MIN(OrderDate) FROM OrderCust))
149
Retrieve max balance of customers who did not
make any orders.
SELECT MAX(Balance) FROM Customer
150
Retrieve name of the customerwho ordered the
most expensive item.
SELECT Name FROM Customer WHERE (CustNum IN
(SELECT Customer.CustNum FROM Customer,
OrderCust, OrderLine, Item WHERE
Customer.CustNum OrderCust.CustNum AND
OrderCust.OrderNum OrderLine.OrderNum AND
Item.ItemNum OrderLine.ItemNum AND
Item.Price (SELECT
MAX(Price) FROM Item)))
151
Retrieve for each order - number and sum of
ordered items (quantity and price).
SELECT OrderNum, SUM (Qty Price) AS
TotalSum FROM OrderLine GROUP BY OrderNum
152
Retrieve for each order number, sum of ordered
items (quantity and price), if the discount is gt
0.
SELECT OrderNum, SUM (Qty Price) AS
TotalSum FROM OrderLine WHERE Discount gt 0 GROUP
BY OrderNum
153
Retrieve for an order number, sum of ordered
items (quantity and price), that sum to be max.
SELECT OrderNum, SUM (Qty Price) AS
TotalSum FROM OrderLine GROUP BY OrderNum HAVING
SUM (Qty Price) gt ALL ( SELECT SUM (Qty
Price) FROM OrderLine GROUP BY
OrderNum)
154
Retrieve customer number, order number and number
of items in the order for the customers from
Paris.
SELECT Customer.CustNum, OrderCust.OrderNum,
count() AS cnt FROM Customer, OrderCust,
OrderLine WHERE Customer.City Paris AND
Customer.CustNum OrderCust.CustNum AND
OrderCust.OrderNum OrderLine.OrderNum GROUP BY
Customer.CustNum, OrderCust.OrderNum
155
SELECT dbo.Customer.CustNum, dbo.OrderCust.OrderNu
m, COUNT() AS cnt FROM dbo.Customer INNER
JOIN dbo.OrderCust ON dbo.Customer.CustNu
m dbo.OrderCust.CustNum INNER JOIN
dbo.OrderLine ON dbo.OrderCust.OrderNum
dbo.OrderLine.OrderNum WHERE (dbo.Customer.City
'Paris') GROUP BY dbo.Customer.CustNum,
dbo.OrderCust.OrderNum
156
Retrieve customer name and difference between his
balance and average of balance of the customers
from the same country.
SELECT X.Name, X.Balance (AVG(Y.Balance) AS
Diffr FROM Customer AS X, Customer AS Y WHERE
X.Country Y.Country GROUP BY X.CustNum, X.Name,
X.Balance
157
Retrieve names of customers from New York which
ordered item hard disk
SELECT FROM WHERE
158
Retrieve city with the biggest number of
customers.
SELECT City FROM Customer GROUP BY City HAVING
(COUNT(CustNum) gt ALL (SELECT
COUNT(CustNum) FROM Customer
GROUP BY City))
159
Retrieve cities where the customers have ordered
computer
SELECT DISTINCT City FROM Customer AS X,
OrderCust AS Y, OrderLine AS Z, Item AS I WHERE
X.CustNum Y.CustNum AND Y.OrderNum
Z.OrderNum AND Z.ItemNum I.ItemNum AND
I.ItemName computer
160
Retrieve countries which all customers have the
same balance.
SELECT FROM WHERE
161
Retrieve customer number and difference between
his balance and average balance of customers from
the same city
SELECT X.CustNum, X.Balance AVG(Y.Balance) FROM
Customer AS X, Customer AS Y WHERE X.City Y.
City AND X.CustNum ! Y.CustNum GROUP BY
X.CustNum, X.Balance
162
Retrieve customer number and name, and difference
between sum of all his orders and average of all
customers orders.
SELECT FROM WHERE
Create View SELECT FROM WHERE
163
Data Warehouse

• Use for analytical applications and queries
• Data mart
• Data mining
• OLAP

164
Data Mart
165
Transactions and Database Security

• Transactions
• Concurrency Control
• Database Security