Transcript Database technology

Database technology
Introduction
ER Modeling
Database objects (intro)
SQL
Key DBMS issues:
Efficiency issues:
• Databases can be very large. Efficient access must be provided despite size.
Simplicity issues:
• Different kind of users have different demands on the DBMS.
Simplicity and advanced possibilities must be available.
Multi-user issues:
• Concurrency - Several users may have simultaneous access to the database.
• Access via views - Each user have access to a subset of the total database.
• Authorization - The access privileges of each user will be limited.
Robustness issues:
• Deadlock must be avoided
• Recovery from crashes with a minimum loss of data.
Database objects
• Tables
• Views
• Data types
• Defaults
• Stored procedures
• Triggers
• Index
.
Tables (Entity)
Primary key
Person
PersonID
Fname
Ename
Income
Address
7108118529
Åsa
Åström
13000
1
7303038564
Markus
Andersson
12300
2
7110220230
Jon
Nilsson
12900
1
Address
AddressID
Street
Streetno Pcode
City
1
Räkstigen
15
93015
Skellefteå
2
Fiolgränd
3
93030
Ursviken
Primary key
Foreign key
Tables cont.
To rows in the same table (entity) cannot have the same values
they are separated by the Primary key.
To connect to tables the primary key of a table has to match the
Foreign key in another table.
A key can be a single column (attribute) or a combination of to or more.
A table shall follow the First normal form, this means that it shall
not be possible to decompose an attribute further.
Structured Query Language
CREATE TABLE person
PersonID INT PRIMARY KEY,
Fname CHAR(20),
Ename CHAR(20),
Income SMALLMONEY,
Adress INT
INSERT [INTO] person(PersonID, Fname, Ename, Income, Address)
VALUES (’7108118529’,’Åsa’,’Åström’,13000, 1)
UPDATE Person
SET Ename = ’Nilsson’
WHERE Ename = ’Åström’
DELETE [FROM] Person
WHERE Fname = ’Åsa’
Integrity/Constraint
1. Entity Integrity
2. Domain Integrity
- To rows in the table cannot have the same values
- Control of data in a column
i.e ”Null-control”
3. Referential Integrity - Secures that there are always a relation between a
Primary key and a Foreign key
CREATE TABLE …
CREATE INTEGRITY (or CONSTRAINT for MS SQL-Server)
ON Person IS
PersonID > 0001010000 AND
PersonID < (Date) * 10000 AND
PersonID <> ’Null’;
OBS! Missing values is stored as ’Null’ this is not the same as ’ZERO’
Views
• A view is just a functional image of one (or several!) database
• Structurally a view is identical with a query
• Functionally it persist in time
it tracks the changes made in the database
• A view may itself be queried
• A view can be used to limit the access
CREATE VIEW Contact
SELECT Fname, Ename, Street, Streetno, Pcode, City FROM Person, Address
WHERE AddressID.Address = Address.Person
SELECT * FROM Contact
Stored Procedures & Triggers
A Stored Procedure is...
… a group of pre compiled SQL-commands stored on the server
… often located in the Procedure Cache
- This leads to great benefits in performance!
A Trigger is…
… a special kind of stored procedure activated by a pre defined action on the server
Database technology (2)
MS SQL Server
Optimizing
Recovery
Communication
MS SQL Server
MS SQL Server...
... Is a DBMS developed for Client/Server applications
… is integrated with NT
(Security, Performance Monitor, Event Viewer and Scheduling services)
… includes replication possibility
… permits central administration of several SQL Servers on a network,
with graphical tools
Tools included in SQL Server
• ISQL/w
• SQL Service Manager
• SQL Security Manager
• SQL Enterprise Manager
• Performance Monitor
• BCP (Bulk Copy Program)
• SQL Server Books Online
And more...
Architecture
• Master database
• Device
• Database ( includes Tables etc and a Transaction log)
• Transaction log
Master database
The Master database…
… is created during the installation of the SQL Server
… includes information on all other databases on the server
… includes information on which devices that are created
… also includes information on the total system
… is the most important database on the server.
If this database disappears you will have to reinstall!
Device
A Device is…
… a physical file (an area on the disk) that can store databases, the transaction log
and security copies of databases (filename.dat)
… can store several databases (and a database can reach over several devices)
… at least 1 MB, it can be increased but not decreased
… created within the Enterprise Manager or by the DISK INIT command
… can be one out of these tree
Dump device
- Backup
Tape dump device - Backup
Database device - Active device
You get higher performance if you choose fewer and bigger devices
It is also easier from an administrators point of view
Database
When creating a database…
… you allocate space on one (or several) devices
… there is automatically created a transaction log
… you have to use the “sa” account
… you will use the Enterprise Manager or the CREATE DATABASE command
Transaction log
The transaction log...
… takes notice about every activity in the database (insert, update, delete)
… controls if a transaction has succeeded or not
Place the transaction log in a separate device makes benefits
• Higher performance
• Separate backup on the transaction log
How to empty a transaction log
1. DUMP TRANSACTION ´dbname´ WHITH NO LOG
2. Backup the database
Recovery routines
Backup
• Hard- and Software problems
• User problems
• Theft, fire…
Generally recommendations:
- Backup the transaction log at least once a day
- Backup the database at least once a week
(this requires that the transaction log and the database is stored in different devices)
- Note in which order devices etc are created and use the same order when restoring
Mirroring instead of Backup
Backup Server
Communication
• BCP - Bulk Copy Program
• Replication
• Mail/Exchange/NT integration
• ODBC - Open Database Connectivity
• Access
• Web technology
• Security