Transcript File

DBMS
Relational Databases….
• Independent Tables
• Relationship through primary & secondary
keys
• SQL
– Select: Creates subset of rows that meet specific criteria
– Join: Combines relational tables to provide users with
information
– Project: Enables users to create new tables containing
only relevant information
• Compatibility with Existing Applications
• Database Security
• Data Dictionaries
What Makes a Database Relational?
• A database is relational when files are
related to each other, such as this Student ID
field in the Student file.
Relational Database
•
•
•
•
•
Data stored in related tables
Table records have same record type
Record = table row
Attributes = table columns (fields)
Uses common key fields for relationships
Table and Record Example
Database Characteristics
Relationship: association between data
stored in different tables
Designing Databases
• Database structure: arrangement of the
fields, tables and relationships
• Design for ease of access and
maintenance in as small a file as possible.
– First determine what data must be collected
and stored
– Next organize data into fields and define how
the field is stored
– Break data into small fields (firstname,
lastname)
Data Types
• Character
• Numeric
– Real and integer
•
•
•
•
•
Date
Logical
Memo
Image
Calculated field
Organizing Table Data
• Sort order: order in which records are
stored on disk
– Queries and updates are faster
– Sort key (one or more fields)
• Database index: list of keys and
associated record numbers
– Doesn’t affect the way records are physically
stored
SQL and Databases
• Structured Query Language that
works behind the scenes of the
database client software
• SQL queries consist of:
– An action
– Database table name
– Set of parameters
SELECT tracktitle FROM tracks WHERE tracktitle = “Fly Away”
Let’s have a closer look at
Structured query language
SQL
SQL basics
• SQL allows you to access a database
• SQL executes queries against a database
• SQL commands are case independent.
– SELECT = select
• But column names or DATA are not case independent.
• SQL command are named after english words:
– Create, select, insert, update ...
– It's easy to learn
3 types of SQL commands
• 1. Data Definition Language (DDL) commands - that
define a database, including creating, altering, and
dropping tables and establishing constraints
• 2. Data Manipulation Language (DML) commands - that
maintain and query a database
• 3. Data Control Language (DCL) commands - that
control a database, including administering privileges
and committing data
DDL, DML, DCL, and the database development process
15
Syntax used in these notes
•
•
•
•
•
Capitals = command syntax
Lowercase = values that must be supplied by user
Brackets = enclose optional syntax
Each SQL command ends with a semicolon ‘;’
In interactive mode, when the user presses the RETURN
key, the SQL command will execute
Data Definition Language: create the tables
• Create table syntax
CREATE TABLE tablename (
column1 data_type [not null] [unique] [column_constraint] ,
...
[table constraints]);
• Example:
CREATE TABLE person (
personID varchar(5) NOT NULL,
Name varchar(25) NOT NULL,
Firstname varchar(15) NULL);
How to judge which table to
create and what could be the
columns (fields)
Remember from the Production System
Case Study: you are transforming an Entity to a Table
Simple entity translation
id
birthday
Actor
name
address
General Rule:
• Create a table with the name of the Entity.
• There is a column for each attribute
• The key in the diagram is the primary key of the
table
Student
id
name
address
name
address
student
id
How to Create tables with SQL
• The columns needed for each table can be defined using
the CREATE TABLE command. The syntax for this is
shown in the upcoming figure. These are the seven
steps to follow:
• 1. Identify the appropriate datatype for each columns,
including length and precision
• 2. Identify those columns that should accept null values.
Column controls that indicate a column cannot be null
are established when a table is created and are enforced
for every update of the table
Creating tables
• 3. Identify those columns that need to be UNQUE - when
the data in that column must have a different value (no
duplicates) for each row of data within that table. Where
a column or set of columns is designated as UNIQUE,
this is a candidate key. Only one candidate key may be
designated as a PRIMARY KEY
• 4. Identify all primary key-foreign key mates. Foreign
keys can be established immediately or later by altering
the table. The parent table in such a parent-child
relationship should be created first. The column
constraint REFERENCES can be used to enforce
referential integrity
Creating tables
• 5. Determine values to be inserted into any columns for
which a DEFAULT value is desired - can be used to
define a value that is automatically inserted when no
value is provided during data entry.
• 6. Identify any columns for which domain specifications
may be stated that are more constrained than those
established by data type. Using CHECK it is possible to
establish validation rules for values to be inserted into
the database
• 7. Create the table and any desired indexes using the
CREATE TABLE and CREATE INDEX statements
Simple entity translation and creating table
id
birthday
Actor
name
address
Relation: Actor (id, name, birthday, address)
create table Actor(id varchar(20) primary key,
name varchar(40),
birthday date,
address varchar(100));
Table creation
General syntax for CREATE TABLE
26
Table creation
• The following Fig. Shows SQL database definition
commands
• Here some additional column constraints are shown, and
primary and foreign keys are given names
• For example, the CUSTOMER table’s primary key is
CUSTOMER_ID
• The primary key constraint is named CUSTOMER_PK,
without the constraint name a system identifier would be
assigned automatically and the identifier would be
difficult to read
SQL database definition commands for Pine Valley Furniture
28
STEP 1
Defining
attributes and
their data types
29
STEP2
Non-nullable
specifications
Note: primary
keys should not
be null
30
STEP 3
Identifying
primary keys
This is a composite
primary key
31
STEP 4
Identifying
foreign keys and
establishing
relationships
32
STEP 7
Overall table
definitions
33
Fig. (a) Order and Order_Line tables
Fig. (b) Product and Customer tables
The final shape of the DDL in this case
Overall table
definitions
36
Constraints
• NOT NULL : the column cannot take the
NULL value
• NULL : the column can take the null value
(default)
• UNIQUE : the column has unique values
Example
CREATE TABLE person (
personID varchar(5) NOT NULL UNIQUE,
Name varchar(25) NOT NULL,
Firstname varchar(15) NULL,
City varchar(20)
DDL: alter table
• Modifying the structure of a table. Add a
column:
ALTER TABLE <tablename> ADD
<column_name>
<data_type> [DEFAULT
<value>] [<constraint>]
DDL: drop table
• Delete a table
DROP TABLE tablename;
• It is very easy and fast to type the drop
table command -> be careful !!!
Data Manipulation Language (DML):
Update Operations
INSERT - inserts a list of attributes .
INSERT INTO PERSON VALUES (008, Tome, Hector, J);
DELETE - removes a row from a table.
DELETE FROM PERSON WHERE ID = 7;
• Attention “DELETE FROM PERSON” will delete
the whole table
Update Operations
UPDATE - changes the value of one or
more attributes in a row.
– When modifying an attribute that is not a
primary key or foreign key, there is usually
no danger of violating a constraint.
– When modifying a primary key, it is
equivalent to doing a delete followed by an
insert operation.
UPDATE PERSON SET ID = 007 WHERE
LastName=‘Tome’ AND FirstName=‘Pat’;
Select
• Select is used to retrieve data :
SELECT [DISTINCT] <column(s)>
FROM <table(s)>
[WHERE <condition>]
[ORDER BY <column(s) [ASC | DESC]>]
– In uppercase are the SQL keywords
– Between [] optional conditions
– Between <> what correspond to your table(s) definition
Select
Select all data
FirstName
SurName
JAMES
KIRK
JAMES
BOND
...
...
SELECT FIRSTNAME, SURNAME
FROM PERSON
Select specific data, ordered
SELECT FIRSTNAME, SURNAME FROM PERSON WHERE
SURNAME = ‘BOND’ORDER BY FIRSTNAME ASC
Select
Select all data where person firstname is
“JAMES”
SELECT * FROM PERSON WHERE FIRSTNAME='JAMES'
PersonID FirstName SurName MidInitial
045
JAMES
KIRK
T
007
JAMES
BOND
X
Select all data where person firstname is “JAMES” and surname is “BOND”
SELECT * FROM PERSON WHERE FIRSTNAME='JAMES'
AND SURNAME = 'BOND'
Select all data where person firstname begins with
“JAM”
SELECT * FROM PERSON WHERE FIRSTNAME like 'JAM%'
PersonID FirstName SurName MidInitial
045
JAMES
KIRK
T
007
JAMES
BOND
X
More examples: special conditions
• Set conditions: <column> [ ] IN (<list of
values>)
– SELECT firstname FROM person WHERE
surname IN ('BOND','KIRK');
Union
Query: Give me the IDs of all people called
‘Smith’, and those who have published in
‘Science’.
SELECT PERSONID FROM PERSON WHERE SURNAME='SMITH
UNION
SELECT PERSONID FROM PUBLICATION WHERE
JOURNAL=
'SCIENCE'
Difference
Query: Give me the IDs of all people called
‘Jones’who don’t have a paper in a journal.
SELECT PERSONID FROM PERSON WHERE SURNAME=‘JONES’
MINUS
SELECT PERSONID FROM PUBLICATION
• The difference of two tables is a third table
that contain those rows that occur in the first
table but not in the second.
What we have Done in SQL
• DDL
– CREATE, ALTER & DROP
• DML
– INSERT, UPDATE, DELETE & SELECT
– Variation of SELECT
• Simple SELECT from a table with Where Condition
• Union
• Difference