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
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))
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;
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
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:
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'
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
SELECT Statement Examples
SELECT Lname,Fname FROM Person
Or:
SELECT Person.Lname, Person.Fname FROM Person
SELECT * FROM Person WHERE City='Sandnes'
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'
SQL AND & OR Operators
• AND operator displays records when all
conditions are true.
• OR operator displays records when any condition is
true.
SQL AND & OR Operators
Example
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)
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)
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
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
DISTINCT Keyword
• The keyword DISTINCT is used to eliminate
duplicate tuples in a query result.
SQL Alias
• It used to give table or column(s) another name.
SQL Alias Example
• If we want to display column called full name that
concatenate between first name and last name.
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 .
SQL TOP Clause
• It is used to specify the number of records to
return.
• Retrieve the first two record from EMPLOYEE table
ORDER BY Keyword
• Ordering of rows displayed
• Opposite order is given by DESC
Thank You