Transcript Data Modeling

Database
Basic Definitions
• Database: A collection of related data.
• Database Management System (DBMS):
A software package/ system to facilitate the
creation and maintenance of a
computerized database.
• Database System: The DBMS software
together with the data itself. Sometimes,
the applications are also included.
( Software + Database )
2
3
Relational Database
4
Data Models
• High Level or Conceptual data models
provide concepts that are close to the
way many users perceive data, entities,
attributes and relationships. (Ex. ERD)
• Physical data models describes how data
is stored in the computer and the access
path needed to access and search for
data.
5
Entity Relationship Diagram
[ERD]
Entity Relationship
Modeling
• Entity-Relationship Diagram (ERD):
Identifies information required by the
business by displaying the relevant entities
and the relationships between them.
7
Entity Relationship
Modeling, (Cont.)
• In building a data model a number of
questions must be addressed:
– What entities need to be described in the
model?
– What characteristics or attributes of those
entities need to be recorded?
– Can an attribute or a set of attributes be
identified that will uniquely identify one specific
occurrence of an entity?
– What associations or relationships exist between
entities?
8
Entity
• An entity is a thing that exists and is
distinguishable -- an object, something in
the environment.
• Examples : book, item, student
• Types of entities:
– Regular entity (Strong entity): if its existence
does not depend on another entity.
– Weak entity: if its existence depends on another
entity.
9
Attribute
•
•
•
•
An entity has a set of attributes
Attribute defines property of an entity
It is given a name
Attribute has value for each entity and value
may change over time
• Example : BOOK entity has the following
attributes (TITLE, ISBN, AUTHOR, PUBLISHER, YEAR, PRICE)
10
Attribute Types
• Simple: Each entity has a single atomic value for
the attribute ; e.g., SSN
• Composite: The attribute may be composed of
several components ; e.g., Name (FirstName,
MiddleName, LastName)
• Multi-valued: The attribute may has more than
one value for a given entity; e.g., a book may have
many authors
• Derived attributes: The attributes whose values
are generated from other attributes using
calculations ; e.g., Age is derived by subtracting
current date from birthdate
11
Key Attribute
• Primary Key: an attribute of an entity type for
which each entity must have a unique value; e.g.,
SSN of EMPLOYEE.
• Composite key: e.g., ID is a key of the applicant
entity type with components (National_ID,
Application_no)
• Foreign Key (referential attributes): Attributes
that define relationships between entities. The
attributes of a foreign key in one entity are the
attributes of a primary key in another entity; e.g.,
Department ID is the primary key of Department and
Department ID is a foreign key of Employee defining
the relationship "Employee works for Department"
12
Relationships
• Relationships: A relationship is a
connection between entity classes.
• The cardinality of a relationship indicates
the number of instances in entity class E1
that can or must be associated with
instances in entity class E2.
– One-One Relationship: (citizen – passport ,
– One-Many Relationship: (student-Advisor,
Customer-Order)
– Many- Many Relationship: (e.g. StudentOrganization, Order-Products)
– Recursive Relationships: A relationship in which
the same entity participates more than once.
13
Relationships, (cont.)
• Binary relationship: between two entity sets
– e.g., binary relationship set STUDY between
STUDENT and COURSE
• Ternary relationship: among three entity sets
– e.g., ternary relationship STUDY could be ternary
among STUDENT, COURSE and TEACHER
– A relationship may have attributes
e.g., attribute GRADE and SEMESTER for STUDY
14
Optional And Mandatory
15
Optional And Mandatory,
(cont.)
16
ERD Notations
• Rectangles represent ENTITY
CLASSES
• Circles represent ATTRIBUTES
• Diamonds represent RELATIONSHIPS
• Arcs connect entities to
relationships. Arcs are also used to
connect attributes to entities. Some
styles of entity-relationship
diagrams use arrows and double
arrows to indicate the one and the
many in relationships.
• Underline - Key attributes of entities
are underlined.
17
Summary Of ERD Notations
18
Example
Add Some Attributes To
Entities
Exercise
• A company is organized into departments. Each
department has a unique name, a unique number,
and a particular employee who manages the
department.
• A department may have several locations.
• A department may control a number of projects,
each of which has a unique name, a unique number,
and a single location.
• A project must controlled by department.
• We store employee’s name, social security number,
address, salary, gender and birth date.
21
Exercise, (cont.)
• An employee must be assigned to one department
and must work on one or more projects, which are
not necessarily controlled by the same department.
• We keep track of the number of hours per week
that an employee works on each project.
• We also keep track of the direct supervisor of each
employee.
• We want to keep track of the dependents of each
employee for insurance purposes.
• We keep each dependent’s first name, gender, birth
date and relationship to that employee.
22
23
ERD Mapping to Tables
ER-to-Relational Mapping
•
•
•
•
Step 1: Mapping of Regular Entity Types
Step 2: Mapping of Weak Entity Types
Step 3: Mapping of Binary 1:1 Relation Types
Step 4: Mapping of Binary 1:N Relationship
Types.
• Step 5: Mapping of Binary M:N Relationship
Types.
• Step 6: Mapping of Multi-valued attributes.
• Step 7: Mapping of N-ary Relationship
Types.
25
Step 1: Mapping of Regular
Entity Types
• Create table for each regular (strong) entity
type.
• Choose one of key attributes to be the
primary key.
26
Step 2: Mapping of Weak
Entity Types
• Create table for each weak entity.
• Add foreign key that correspond to the
owner entity type.
• Choose the primary key : ( FK + weak entity
Partial PK if any).
27
Step 3: Mapping of Binary
1:1 Relation Types
1. Merged two tables if both sides are
Mandatory.
2. Add FK into table with the Mandatory
relationship to represent optional side.
3. Create a third table with just the keys from
the two tables in addition to the two table
if both sides are optional.
28
Step 4: Mapping of Binary
1:N Relationship Types
• If the N-side is mandatory, add FK to N-side
table
Add any simple attributes of relationship as
column to N-side table.
• If the N-side is optional, create a third table
The of the new table consists of a
concatenation of the keys of the related
entities. Include any attributes that were in
the relationship.
29
Step 5: Mapping of Binary
M:N Relationship Types
• Create a new third table
• Add FKs to the new table for both parent
tables
• Add simple attributes of relationship to the
new table if any .
30
Step 6: Mapping of
Multi-valued attributes
• Create new table for each multi-valued
attribute
• Table will include two columns:
one for multi-valued attribute + FK column.
31
Step 7: Mapping of N-ary
Relationship Types
• If n > 2 then :
• Create a new third table
• Add FKs to the new table for all parent
tables
• Add simple attributes of relationship to the
new table if any .
32
Mapping of N-ary
Relationship (Example)
33
Referential Integrity
• Referential Integrity requires that:
– The columns of a foreign key must match in type
the columns of the primary key in the
referenced table.
– The values of the foreign key columns in each
row of the referencing table must match the
values of the corresponding primary key
columns for a row in the referenced table.
34
Referential Integrity, (cont.)
35
Referential Integrity, (cont.)
• No Action
– only primary keys with no matching foreign keys can be
deleted/Updated
– the change to the referenced (primary key) table is not
performed.
• Cascade
where the foreign key in the case matching is
deleted/updated.
– For update (the primary key column values have been
modified), the corresponding foreign key columns for
referencing rows are set to the new values.
– For delete (the primary key row is deleted), the referencing
rows (foreign key) are deleted
36
Referential Integrity, (cont.)
• SET NULL
– All matching foreign keys are set to null, and
delete/update then takes place in other relation.
• SET Default
– all matching foreign keys are set to default value, and
delete/update then takes place in other relation.
37
38
Mapping Result
39
SQL Basics
Data Definition Language
(DDL)
 Create Table
 Drop Table
 Alter Table
41
Create Table
• The Create table statement is used to
create a table in a database and to add
constraints .
– Constraints:
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT
42
Create Table Example
CREATE TABLE person( id int,
name varchar(50) not null,
email varchar(50),
age int default 18,
Dept_ID int,
primary key(id),
check(age>17),
unique(email),
foreign key (Dept_ID) REFERENCES
Department(Dept_ID))
43
Drop Table
• Used to remove a relation (base table) and
its definition
• The relation can no longer be used in
queries, updates, or any other commands
since its description no longer exists
• Example:
DROP TABLE
DEPENDENT;
44
Alter Table
• Used to modify the table design like add a
column, modify a column or delete a
column.
• Examples:
ALTER TABLE EMPLOYEE
VARCHAR(12)
ALTER TABLE EMPLOYEE
JOB
VARCHAR(50)
ALTER TABLE EMPLOYEE
ADD
JOB
ALTER COLUMN
DROP COLUMN
JOB
45
Data Manipulation Language
(DML)
 SELECT
o Extracts data from a database
 INSERT INTO
o Inserts new data into a database
 UPDATE
o Updates data in a database
 DELETE
o Deletes data from a database
46
INSERT INTO Statement
• First form:
It doesn't specify the column names where
the data will be inserted, only their values.
INSERT INTO Employee VALUES (4,'Ahmed',
‘Ali', 112233,
'1965-01-01 ', '15 Ali fahmy St.Giza‘,
‘M’,1300,223344,10)
– The previous form the attribute values should be
listed in the same order as the attributes were
specified in table.
47
INSERT INTO Statement,
(cont.)
• Second form:
It specifies both the column names and the
values to be inserted
INSERT INTO Employee (SSN, Lname, fname)
VALUES (5, ‘Ahmed', ‘Ali')
48
INSERT INTO Statement,
(cont.)
• To insert a new row into table with identity value,
we will not have to specify a value for the identity
column (a unique value will be added
automatically):
insert into Department values('DP2','56733')
insert into Department(Dname,MGRSSN)
values('DP2','56733')
• The Result:
49
UPDATE Statement
• The UPDATE statement is used to update existing
records in a table.
• SQL Syntax
– UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
• Note: The WHERE clause specifies which record or
records that should be updated. If you omit the
WHERE clause, all records will be updated!
UPDATE Person SET Address='18 Abaas El 3akaad
St. Nasr City.Cairo', City='Cairo' WHERE
Lname=‘Amr' AND Fname='Ali'
50
DELETE Statement
• The DELETE statement is used to delete
rows in a table.
delete from Employee
where Lname='Brown’
delete from Employee
51
SELECT Statement
• Basic form of the SQL SELECT statement is
called a mapping or a SELECT-FROM-WHERE
block
SELECT
FROM
WHERE
<attribute list>
<table list>
<condition>
– <attribute list> is a list of attribute names whose
values are to be retrieved by the query
– <table list> is a list of the relation names required
to process the query
– <condition> is a conditional (Boolean) expression
that identifies the tuples to be retrieved by the
query
52
SELECT Statement Examples
SELECT Lname,Fname FROM Person
Or:
SELECT Person.Lname, Person.Fname FROM Person
SELECT * FROM Person WHERE City='Sandnes'
53
LIKE Operator Example
SELECT * FROM Person WHERE City LIKE
's%'
SELECT * FROM Person WHERE City LIKE
'%s'
Used to represent one character
SELECT * FROM Person WHERE City LIKE
'_andnes'
54
SQL AND & OR Operators
• AND operator displays records when all
conditions are true.
• OR operator displays records when any condition is
true.
55
SQL AND & OR Operators
Example
56
IN Operator Example
• Retrieve data of all employees whose social
security numbers number is 112233, or 512463.
– Query :
SELECT * FROM Employee WHERE SSN IN
(112233,512463)
57
IN Operator Example
• Retrieve data of all employees whose social
security numbers number is not 112233, or
512463.
– Query :
SELECT * FROM Employee WHERE SSN not IN
(112233,512463)
58
The BETWEEN Operator
• The BETWEEN operator is used in a WHERE clause
to select a range of data between two values.
• Retrieve the employees with salary between 1000
and 2000
– Query :
SELECT * FROM EMPLOYEE
WHERE SALARY BETWEEN 1000 AND 2000
59
NULLS IN SQL QUERIES
• SQL allows queries that check if a value is NULL.
• SQL uses IS or IS NOT to compare Nulls.
• Retrieve the names of all employees who do not
have supervisors.
– Query :
SELECT FNAME, LNAME
FROM
EMPLOYEE
WHERE SUPERSSN IS
NULL
60
DISTINCT Keyword
• The keyword DISTINCT is used to eliminate
duplicate tuples in a query result.
61
SQL Alias
• It used to give table or column(s) another name.
62
SQL Alias Example
• If we want to display column called full name that
concatenate between first name and last name.
63
Note
• When we want to concatenate between columns,
all columns should have the same data type.
• If we want to concatenate between two columns
that have different data type , use convert()
function that convert from data type to another .
64
SQL TOP Clause
• It is used to specify the number of records to
return.
• Retrieve the first two record from EMPLOYEE table
65
ORDER BY Keyword
• Ordering of rows displayed
• Opposite order is given by DESC
66
Joining Data from Multiple
Tables
67
Join Types
Inner Join
–The INNER JOIN keyword return rows when there is
at least one match in both tables.
Outer Join
–Includes left, right, or full outer joins
Cross Join
–Also called Cartesian products
Self Join
–Refers to any join used to join a table to itself
68
Joining Data Using Inner Join
The INNER JOIN returns rows when there is at least
one match in both tables.
69
Joining Data Using Inner Join
Example
Retrieve list of all employees with their departments
70
Applying Additional
Conditions to join
Example:
For each project located in Cairo City , find the project name, the controlling
department name
71
LEFT OUTER JOIN
The LEFT JOIN keyword returns all rows from the left
table (table_name1), even if there are no matches in the
right table (table_name2).
There are no employees
in department 30.
72
LEFT OUTER JOIN Example
73
RIGHT OUTER JOIN
The RIGHT JOIN keyword returns all the rows from the
right table (table_name2), even if there are no matches
in the left table (table_name1).
74
RIGHT OUTER JOIN Example
75
FULL JOIN
The FULL JOIN keyword return rows when there is a
match in one of the table.
Example:
76
Cartesian Product
• A Cartesian product is formed when:
– A join condition is omitted
– All rows in the first table are joined to all rows in
the second table
• To avoid a Cartesian product, always include
a valid join condition
77
Generating a Cartesian
Product
EMPLOYEES (6 rows)
DEPARTMENTS (3 rows)
Cartesian product:
6 x 3 = 18 rows
78
Creating Cross Join
• The CROSS JOIN clause produces the crossproduct of two tables.
• This is also called a Cartesian product between
the two tables.
SELECT Employee.Fname,Employee.SSN, Department.DNumber
FROM Department Cross JOIN Employee
79
Self-Join Using the ON
Clause
EMPLOYEE
EMPLOYEE (Worker)
EMPLOYEE (Supervisors)
Superssn in the WORKER table is equal to SSN in the Supervisors table
80
Self-Joins Using the ON
Clause
Retrieve list of all employees with their supervisors
81
Joining Three or More Tables
FROM clauses can contain multiple Join specifications
which allows many tables to be joined in a single
Query
82
Joining Three or More Tables
Example
For each project, find the project number, the controlling
department name, the department manager last name
83
Functions
84
SQL NULL Functions
ISNULL( ) Function
It is used to specify how to display null values in
column(s)
Note: the value type should be the same data type of
column name
85
SQL NULL Functions
Examples
86
Aggregate Functions
Group functions operate on sets of rows to give one
result per group
Maximum salary in
EMPLOYEES table
87
Types of Group Functions
avg: average value
count: number of values
min: minimum value
Group
functions
max: maximum value
sum: sum of values
88
Using the AVG and SUM
Functions
The AVG() function returns the average value of a
numeric column
The SUM() function returns the total sum of a numeric
column
Find the average and the summation of salary among all
employees.
89
SQL COUNT Function
COUNT(*) returns the number of rows in a table
COUNT(column) returns the number of rows with nonnull values for the column
90
Using the MIN and MAX
Functions
• The MAX() function returns the largest
value of the selected column.
• The MIN() function returns the smallest
value of the selected column
• You can use MIN and MAX for numeric,
character, and date data types
91
Using the MIN and MAX
Functions Examples
92
Example
Find the maximum salary, the minimum salary, and the
average salary among employees who work for the ‘DP1'
department.
93
Group Functions and Null
Values
Group functions ignore null values in the column
94
Creating Groups of Data
• In many cases, we want to apply the aggregate functions to
subgroups of rows in a table
• SQL has a GROUP BY clause for specifying the grouping
attributes, which must also appear in the SELECT-clause
95
Using the GROUP BY Clause
on Multiple Columns
For each department, retrieve the department number,
the number of employees in the department
96
Illegal Queries
Using Group Functions
Any column or expression in the SELECT list that
is not an aggregate function must be in the
GROUP BY clause
Column missing in the GROUP BY clause
97
HAVING clause
Specifies a search condition for a group
Cannot use the WHERE clause to restrict groups
98
Summary
• In this lesson, you should have learned how to:
– Use the group functions COUNT, MAX, MIN,SUM
and AVG
– Write queries that use the GROUP BY clause
– Write queries that use the HAVING clause
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
column, group_function
table
condition]
group_by_expression]
group_condition]
column];
99
String Functions
String
functions
Case-manipulation
functions
LOWER
UPPER
Character-manipulation
functions
CONCAT
SUBSTRING
REPLACE
LTRIM
100
Case-Manipulation
Functions
These functions convert case for character strings:
Function
LOWER('SQL Course')
Result
sql course
UPPER('SQL Course')
SQL COURSE
Example:
101
Character-Manipulation
Functions
These functions manipulate character strings:
Function
SUBSTRING('HelloWorld',1,5)
Result
Hello
REPLACE
('JACK and JUE','J','BL')
BLACK and BLUE
lTRIM('
Hello
Hello')
102
Character-Manipulation
Functions Examples
103
Date Functions
It is built in function used to get date or time
Function
GETDATE ( )
Result
returns current date and time
DATENAME ( datepart ,
date )
Returns a character string that
represents the specified datepart of
the specified date
DATEDIFF ( datepart ,
startdate , enddate )
Returns the number of date or time
datepart boundaries that are crossed
between two specified dates.
DATEADD (datepart ,
number , date )
Returns a new datetime value by adding
an interval to the specified datepart of the
specified date.
104
GETDATE Function Example
select current date and time
105
DATENAME Function
DATENAME ( datepart , date )
Datepart Is the part of the date to return .
datepart
Abbreviations
year
yy, yyyy, year
month
mm, m , MONTH
day
D,DD, DAY
Hour
HH
Minute
MI
Second
SS
106
DATENAME Function
Example
select current month
from Employee table we want to know the employees
who born in septemper
107
DATEDIFF Example
DATEDIFF ( datepart , startdate , enddate )
Display the last name and birthrate of every employee
whose age is greater than 35.
108
DATEADD Example
DATEADD (datepart , number , date )
add 5 days to each Bdate.
109
Thank You