1 / 19

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:

ayita
Download Presentation

Database technology

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database technology Introduction ER Modeling Database objects (intro) SQL

  2. 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.

  3. Database objects • Tables • Views • Data types • Defaults • Stored procedures • Triggers • Index • .

  4. Tables (Entity) Primary key Person Address Primary key Foreign key

  5. 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 shallnot be possible to decompose an attribute further.

  6. 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’

  7. Integrity/Constraint 1. Entity Integrity - To rows in the table cannot have the same values 2. Domain Integrity - 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’

  8. 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 accessCREATE VIEW Contact SELECT Fname, Ename, Street, Streetno, Pcode, City FROM Person, Address • WHERE AddressID.Address = Address.Person • SELECT * FROM Contact

  9. 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

  10. Database technology (2) MS SQL Server Optimizing Recovery Communication

  11. 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

  12. 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 OnlineAnd more...

  13. Architecture • Master database • Device • Database ( includes Tables etc and a Transaction log) • Transaction log

  14. 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!

  15. 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 devicesIt is also easier from an administrators point of view

  16. 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

  17. 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 LOG2. Backup the database

  18. 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

  19. Communication • BCP - Bulk Copy Program • Replication • Mail/Exchange/NT integration • ODBC - Open Database Connectivity • Access • Web technology • Security

More Related