Transcript name - ShareStudies.com

1
• Design of databases.
• E/R model (entity-relationship model)
• Relational model (tables)
• UML (unified modeling language)
• Database programming.
• SQL, Relational algebra
• Interface from Java and .NET framework
• Data security.
2
• Database Systems: The Complete Book,
2/E ISBN-13: 9780131873254
NOTE: THE BOOK IS A MUST
• Some handouts given by the instructor
• Power point presentations (not sure!)
3
• Test1: 15% Midterm: 20% Final: 35%
• Project: 20%. Any project supported by a
database is acceptable.
• Individual
• Assignments: 10%. Exercises from the book
in addition to some challenging ones.
• Individual – No late policy
4
•
•
•
•
•
•
•
SOE rules and regulation
Important dates
Absences and late policy
Email usage
SP courses policy
Labs
Office hours
5
•
•
•
•
•
Do not come to lectures
Do not do assignments or start late
Do not ask questions in class
Do not come to see me during office hours
Cheating is also an option
6
• In the LAB we will use:
•
•
•
•
Microsoft SQL server 2008
Oracle database 11g
Visual studio
Java JDK 6
• Each of you should duplicate this environment
on his home computer. It is needed for
assignments and practices.
• An 8GB flash memory is required to exchange
data
7
Attributes
(column
headers)
Tuples
(rows)
name
Corolla
CR-V
mark
Toyota
Honda
Cars
Relation
name
8
• Relation schema = relation name and attribute
list.
• Optionally: types of attributes.
• Example: Cars(name, mark) or Cars(name: string,
mark: string)
• Database = collection of relations.
• Database schema = set of all relation schemas
in the database.
9
• Very simple model.
• Often matches how we think about data.
• Abstract model that underlies SQL, the most
important database language today.
10
• Underline indicates key attributes.
Cars(name, mark)
Garage( name, addr, license)
Customers(name, addr, phone)
Drives(customer, car)
Serves (garage, car, service, price)
Frequents( customer, garage)
11
• SQL is primarily a query language, for
getting information from a database.
• But SQL also includes a data-definition
component for describing database
schemas.
12
• Simplest form is:
CREATE TABLE <name> (
<list of elements>
);
• To delete a relation:
DROP TABLE <name>;
13
• Most basic element: an attribute and its
type.
• The most common types are:
•
•
•
•
INT or INTEGER (synonyms).
REAL or FLOAT (synonyms).
CHAR(n ) = fixed-length string of n characters.
VARCHAR(n ) = variable-length string of up to n
characters.
14
CREATE TABLE Serves (
garage CHAR(20),
car
VARCHAR(20),
service VARCHAR(20),
price REAL
);
15
• Integers and reals are represented as you
would expect.
• Strings are too, except they require single
quotes.
• Two single quotes = real quote,
e.g., ’Tools’’ garage’.
• Any value can be NULL.
16
• DATE and TIME are types in SQL.
• The form of a date value is:
DATE ’yyyy-mm-dd’
• Example: DATE ’2007-09-30’ for Sept. 30,
2007.
17
• The form of a time value is:
TIME ’hh:mm:ss’
with an optional decimal point and fractions
of a second following.
• Example: TIME ’15:30:02.5’ = two and a
half seconds after 3:30PM.
18
• An attribute or list of attributes may be
declared PRIMARY KEY or UNIQUE.
• Either says that no two tuples of the
relation may agree in all the attribute(s) on
the list.
• There are a few distinctions to be
mentioned later.
19
• Place PRIMARY KEY or UNIQUE after the
type in the declaration of the attribute.
• Example:
CREATE TABLE Cars (
name
CHAR(20) UNIQUE,
mark
CHAR(20)
);
20
• A key declaration can also be another
element in the list of elements of a CREATE
TABLE statement.
• This form is essential if the key consists of
more than one attribute.
• May be used even for one-attribute keys
21
• The garage, car and service together are the
key for Serves:
CREATE TABLE Serves(
garage
CHAR(20),
car
VARCHAR(20),
service VARCHAR(20),
price
REAL,
PRIMARY KEY (garage,car,service)
);
22
1. There can be only one PRIMARY KEY for a
relation, but several UNIQUE attributes.
2. No attribute of a PRIMARY KEY can ever
be NULL in any tuple. But attributes
declared UNIQUE may have NULL’s, and
there may be several tuples with NULL.
23
• Another data model, based on trees.
• Motivation: flexible representation of data.
• Motivation: sharing of documents among systems
and databases.
24
• Nodes = objects.
• Labels on arcs (like attribute names).
• Atomic values at leaf nodes (nodes with no
arcs out).
• Flexibility: no restriction on:
• Labels out of a node.
• Number of successors with a given label.
25
Notice a
new kind
of data.
root
car
garage
car
mark
name
servedAt
name
Tools
Tiida
A.B.
mark
name
Tiida
prize
year
1995
award
Gold
addr
Bchamoun
The car object
for Tiida
The garage object
for Tools’ garage
26
•
From the start menu, select
SQL Server Management Studio
•
In the connect to server window select Connect
You are now connected to the
SQL server.
27
•
•
•
From the main menu, select New Query
You are now ready to create your first database
Write the following query
create database [CarService]
GO
Now click on Execute to run the query
28
•
•
In the object explorer window, expand the databases
Notice the creation
of your database
Expand the CarService
database
29
• We need now to create the following tables:
Cars(name(20), mark(20))
Garage( name(20), addr(40), license(10))
Customers(name(40), addr(40), phone(9))
Drives(customer(40), car(10))
Serves (garage(20), car(20), service(10), price())
Frequents( customer(40), garage(20))
Underlined attributes are primary keys
30
• Let’s start with the Cars table Cars(name(20), mark(20))
use [CarService]
create table Cars (
name CHAR(20),
mark CHAR(20),
primary key (name)
);
• Similarly create the other tables
Garage( name(20), addr(40), license(10))
Customers(name(40), addr(40), phone(9))
Drives(customer(40), car(10))
Serves (garage(20), car(20), service(10), price())
Frequents( customer(40), garage(20))
31