Transcript document
DataBase Technology by Dr.S.Sridhar, Ph.D.(JNUD), RACI(Paris, NICE), RMR(USA), RZFM(Germany) DIRECTOR ARUNAI ENGINEERING COLLEGE TIRUVANNAMALAI Lecture by Prof.Dr.S.Sridhar 1 DBMS CONCEPTS Data Base Management Systems • • • • • • • • • • • Examples of Databases Files & DB – Systems Advantages of DBMS Attributes / Entities Normalization in RDBMS RDBMS & Examples SQL in DB & Examples Entity-relationship model 3NF Students/s DB – Case Study Data Base Security / Backups Lecture by Prof.Dr.S.Sridhar 2 • What’s Database System ? - It is nothing but a computerized record keeping system. - The DB itself can be regarded as electronic filing cabinet. - It is a collection of files. The user of the system will have the following facilities : • Adding New, empty files to the DB. • Inserting new data into existing files. • Retrieving data from existing files. • Deleting data from existing files. • Removing existing files, empty or otherwise, from the DB Data base is a collection of files. Data base is a collection of records. Data base is a collection of fields. Lecture by Prof.Dr.S.Sridhar 3 BI N WINE PRODUCER YEAR BOTTLE S REA DY 2 Chardonnary Buena Vista 1992 1 1994 3 Chardonnary Geyser Peak 1992 5 1994 6 Chardonnary Stonestreet 1991 4 1993 12 Jo.Riesling Jekel 1993 1 1994 21 Fune ‘ Blanc Ch.St.Jean 1992 4 1994 22 Fune ‘ Blanc Geyser Peak 1991 2 1993 30 Cab.Saurigno n Jekel 1986 12 1995 43 Cab.Saurigno n Jekel 1990 2 1993 45 Pinot Noir Buena Vista 1989 9 1998 50 Pinrot Noir Buena Vista 1990 2 1998 • The above file is referred as ‘Table’ in RDBMS – Sense. • Call this table as “CELLAR” • This contains many records. • Each record has fields. • Here BI WIN PRODUC YEA • is a record N E ER R • The values inside the table Lecture by are called “ Field values”Prof.Dr.S.Sridhar BOTTLE S READ Y 4 Fields Retrieve Data (Query Language) SELECT WINE, BIN, PRODUCER FROM CELLAR WHERE READY = 1995 ; RESULT WINE Cab.Saurig non BIN 30 PRODUCER JEKEL is the command used to Retrive from which table ( file ) Condition INSERT: INSERT INTO CELLAR ( BIN, WINE, PRODUCER ) VALUES ( 53, ‘Pinot Noir’, ‘Saintsbury’); UPDATE: UPDATE CELLAR SET BOTTLES = 4 WHERE BIN = 3; Lecture by 5 Prof.Dr.S.Sridhar DELETE DELETE FROM CELLAR WHERE BIN = 2; Thus, the commands like, SELECT, INSERT, UPDATE and DELETE are actually the examples of Database Query Language. SQL – Structured Query Language Remarks : - One should be very careful. Whenever DELETE or UPDATE operations are done in the DB. Here, - Retrieving any amount of fields for a particular record is done . - Inserting into file for a particular field(s) with values is done. - Updating is for a particular field in a record is done. - Deleting is done for a record under a matching condition. Lecture by Prof.Dr.S.Sridhar 6 (DBMS) Data Base Management System Data Base Application Programs END USERS Major Components: * Data * Hardware Simplified View * Software of Data base System * Users An Overview of Database Management Lecture by Prof.Dr.S.Sridhar 7 Examples of Data Bases: Financial Database Personnel Database ( Employee) Payroll Database Accounting Database Inventory Database Management Information Database Library Database and so on. Examples of DBMS (Data Base Management System) ORACLE INFORMIX INGRESS SYBASE and so on. What ‘s Data ? It is the meaning full information stored in computer system. Two Kinds Integrated Shared Integrated, means that the Database can be thought of as a unification of several otherwise distinct data files, with any redundancy among those files wholly or partially. Lecture by Prof.Dr.S.Sridhar 8 Example A Database containing both the files. EMPLOYEE IDNO Name Address Department Salaries Phone No. Permanent Address and a training file. TRAINING IDNO Course a Headed Course Details Course Fees Location of Course Feedback Grade Offered Shared Database means that individual pieces of data in the database can be shared by several different users. For example from the file ‘EMPLOYEE’, The general details can be utilised by Personnel department. The salary details may be utilized by Financial department. All done in shared manner. and so on. Lecture by Prof.Dr.S.Sridhar 9 Hardware Consisting of Secondary storage volumes that are used to hold the stored data with associated I/O devices(Disk drives etc), Devices Controllers, I/O channels,etc. The Processors and associated main memory that are used to support the execution of the Database system software. Software Consisting of Database Manager (DB Manager) or more commonly used term as ‘DBMS’, Data Base Management System. Additional utilities like Application development tools, design aids, report writers and so on. Example: Oracle, Informix, Sybase,etc. Users Application Programmers, who are responsible for writing programs to access the Database for Insert, Update, Delete, Query, Reports etc. End users who are really accessing the database for queries / data entry / report generation etc. Lecture by Prof.Dr.S.Sridhar 10 What is Database ? It is a collection of ‘Persistent data’ that is used by the application systems of some given enterprise. Input data : refers the information entered into the systems for the very first time ( typically from a terminal or workstation). It may be part of persistent data but it is not initially part of the databases Output Data : refers for messages and results coming out from the system ( typically printed or displayed on a screen). Again such information is derived from persistent data but it is not itself considered to the part of the database. Enterprise (Egs) ************ A Manufacturing Company A Bank A Hospital A University A govt. Dept. Persistent Data (Egs) **************** * Product Data * Account Data * Patient Data Lecture by Prof.Dr.S.Sridhar 11 Advantages of Databases Compactness ( No read for Voluminous papers) Speed ( for retrieval of Queries / Reports etc) Currency (Accurate, up-to-date information is available on demand anytime ) Centralized control of data. The foregoing benefits apply even more force in multi – user environment, of course, where the database is likely to be much larger and much more complex then in the single-user-case. Redundancy of data can be avoided. In consistency can be avoided to some extent. The data can be shared by many users. Standards can be enforced. Security restrictions can be applied. Integrity can be maintained. Conflicting requirement can be balanced. Lecture by Prof.Dr.S.Sridhar 12 RDBMS (Relational Data Base Mgmt. System) Relational Systems are such that 1. The data perceived by users as tables. 2. The operators are at users’ disposal.(eg) Data retrieval – are operators that qurerate new tables from old and those operators include atleast ‘SELECT’, PROJECT and JOIN. EXAMPLE : DEPT DEPT# DNAME BUDGET D1 D2 D3 Marketing Development Research 10M 12M 5M EMP EMP ENAME # DEPT Salary E1 E2 E3 E4 D1 D1 D2 D2 40k 25k 42k 35k John Marsal Lemark Cheng Lecture by Prof.Dr.S.Sridhar 13 SELECT – Operation extracts specified rows from a table. PROJECT - Operation extracts specified columns from a table JOIN - Operation joins together two tables on the basis of common values in a common column. Apply the above operations on DEPT & EMP Tables SELECT DEPTs WHERE BUDGET > 8M Result DEPT# DNAME BUDGET (Specified Rows) D1 D2 Marketing Development 10M 12M PROJECT DEPTs OVER DEPT#, BUDGET DEPT# BUDGET Result (Specified Columns) D1 10M D2 D3 JOIN DEPTs and EMPs OVER DEPT# Result DEP DNAME BUD Common T# GET Rows D1 Marketing 10M Common Marketing 10M Columns D1 D2 D2 Development 12M Development Lecture by12M Prof.Dr.S.Sridhar 12M 5M EM ENAM P# E E1 E2 E3 E4 SAL John 40K Marsal 25K Lemark 42K Cherq 14 35K Concept ENTITY Entity – Attributes –Relationship models Information Examples Definition A Distinguishable object PROPERTY A piece of information to describe an entity RELATIONSHIP An entity that serves to interconnect 2 or more entities - Supplier - Part - Person - Purchase Order and so on - Supplier number - Shipment Qty - Person height - P.O.data etc. - Shipment (Supplier-part) - Assignment (Emp-Dept.) Here Note 1) Shipment is RELATIONSHIP between supplier and part. R Supplier R Part Similarly for Assignment. Lecture by Prof.Dr.S.Sridhar 15 Department Dept-Emp Emp Employee Ename Emp-Dep First MN Salary Last Dependent Entity / Relationship diagram (Example) -----*---------*----------*--------*---------* Entities : Department, Employee, Dependent Relations : Dept-Emp, Emp-dep Attributes : ( to employee) Emp#, Ename,Salary First MN Last Lecture by (GroupProf.Dr.S.Sridhar item) 16 Properties: Entities have properties : (Known as attributes). Take the case of Employee whose properties are Emp#, Ename and Salary. (all these are attributes). Simple a Composite: Emp# is simple But Ename contains First, MN and Last, three simple items groped together to address Ename. Such type of attributes are ‘Composite’. Key : It is one of the uniquely defined attributes. For eg. Emp# is unique and that is called ‘key’ to access the other properties of the entity employee, like Ename and Salary. Emp# Ename Salary Key Lecture by Prof.Dr.S.Sridhar 17 Types of Relations 1–1 (One to One) 1–M (One to Many) M–1 (Many to One) M – M ) (Many to Many) Examples ( 1-1) For every employee there is only one seat R Emp Seat (1-1) In an organization. (1-M) R Customer1 ; . ; Supplier Customer-m (M-1) R Student1 : : ; Teacher Student-m (M – M) Suplier 1…….m Lecture by Prof.Dr.S.Sridhar Customer1 1………m 18 Many Examples of Attributes / Entities & Relations Example 1: Book –Student BOOK-STD R> R Student Book R< STD -BOOK Entities : Book, Student Relation : BOOK-STD and STD-BOOK Attributes of Book Author Title Publisher Year ISBN Number Number of pages Price Printer Location of printing Edition-Nature Remarks Attributes of Student RollNo ExamNo Name Class Address Phone No Remarks Lecture by Prof.Dr.S.Sridhar 19 Example 2 (R) Part-Sup (R) Part Entities Relations Supplier Sub-part (R) : Part, Supplier : Part-Sup and Sub-part Attributes of part Supplier PartNo Description Qty Unit price Opening balance Parts-new Net balance Remarks Attributes of Supplier code Supplier address Supplier phone Supplier location Delivery time Address demand Warranty Period Remarks Lecture by Prof.Dr.S.Sridhar 20 Example 3: Student Entities Relations Stud-fac R R R Fac-stud Faculty : Student and Faculty : Stud-fac and Fac-stud Attributes of Student RollNo ExamNo Name Handling Class Address PhoneNo Faculty Division Remarks Attributes of Faculty Facuty ID Name Classes Qualification Designation Address Phone No Students Related for Advising Lecture by Prof.Dr.S.Sridhar 21 NORMALIZATION in RDBMS The logical design considered are: S ( S#, SNAME, STATUS, CITY) PRIMARY KEY ( S#) P (P#, PNAME,COLOR, WEIGHT, CITY) PRIMARY KEY (P#) SP (S#, p#,QTY) PRIMARY KEY (S#, P#) FOREIGN KEY ( S#) REFERENCE S FOREIGN KEY (P#) REFERENCE P SCP (Relation) Table S# CITY P# QTY S1 S1 S1 S1 S1 S1 S2 S2 S3 S4 S4 S4 London London London London London London Paris Paris Paris London London London P1 P2 P3 P4 P5 P6 P1 P2 P2 P2 P4 Lecture by Prof.Dr.S.Sridhar P5 300 200 400 200 100 100 300 400 200 200 300 400 22 From the table we find ‘Redundancy’ of data. So a good design principle is “ One fact in one place” ((ie) avoid redundancy). How to do this ? That process is known as “ Normalization”. There are 3NF (Normal forms – NF). Namely First NF (1NF) Second NF (2NF) Third NF (3NF) 1NF: Every Normalized relation is in 1NF if and only if, if satisfied the condition that it contains scalar values ONLY. Example FIRST (S#, STATUS, CITY, P#, QTY) PRIMARY KEY (S#, P#) Functional Dependencies in relation FIRST S# CITY QTY P# STATUS Lecture by Prof.Dr.S.Sridhar 23 Sample Tabulation of FIRST S# STATUS S1 S1 S1 S1 S1 S1 S2 S2 S3 S4 S4 S4 20 20 20 20 20 20 10 10 10 20 20 20 CITY London London London London London London Paris Paris Paris London London London P# P1 P2 P3 P4 P5 P6 P1 P2 P2 P2 P4 P5 QTY 300 200 400 200 100 100 300 400 200 200 300 400 Now problems, We face with INSERT DELETE UPDATE Lecture by Prof.Dr.S.Sridhar 24 Problems with INSERT - For examples, while inserting S5 after S! or S2 or S3 or S4, Confusion arises, Where to insert ?! - Insert a status after 20 – problem ! - Insert a city after LONDON – problem ! - Insert P# after P2 – Problem ! DELETE While deleting some record, One should be more careful. Now delete a record corresponding to S# = 1. This will delete all records corresponding to S# = 1, whereas user may NOT mean this ! Similarly, UPDATE : Update city for S# = 2 This will update all city values, corresponding to S# = 2, whereas user does not mean this ! Lecture by Prof.Dr.S.Sridhar 25 Second Normal Form : A relation is in 2NF if and only if is in !NF and every non-key attribute is irreducibly dependent on the primary key. Example SECOND (S#, STATUS, CITY) and SP(S#, P#, OTY) CITY S# QTY S# STATUS P# Functional Dependencies in relation SECOND AND SP S# P# QTY S1 S1 S1 S1 20 London S1 S2 10 Paris S1 S1 S3 10 Paris S2 S4 20 London S2 S5 30 Athens S3 S4 SAMPLE TABULATION OF Lecture by S4 SECOND AND SP Prof.Dr.S.SridharS4 P1 P2 P3 P4 P5 P6 P1 P2 P2 P2 P4 P5 300 200 400 200 100 100 300 400 200 200 300 400 SECOND S# Status City 26 INSERT : Eventhough S5 is not supplying any parts, we can insert the information that S5 is located in Athens, in SECOND. DELETE : While deleting the shipment connecting S3 and P2 in SP, we do NOT loose information that S3 is located in PARIS. UPDATE : In the revised structure, the city for given supplier appears once not many times. Thus S# CITY redundancy is eliminated. For example we can change for S1, the city as Amsterdam from London. This way updation is also possible in 2NF. Third Normal Form (3NF): A relation is in 3NF if it is in 2NF and every non-key attribute is non-transitively dependent on the primary key. This increases the data independance,. Lecture by Prof.Dr.S.Sridhar 27 Example: Key S# CITY S1 S2 S3 S4 S5 London Paris Paris London Athens Key CITY STATUS Anthen London Paris Rome 30 20 10 50 Sample Tabulation of SC and CS The above relation SC and CS are in 3NF. Whereas, SECOND is not in 3NF. SECOND S# STATUS CITY S1 S2 S3 S4 S5 20 10 10 20 30 London Paris Paris London Athens Thus SECOND (S#, STATUS, CITY) is tabulated in 3NF as Lecture by 28 SC(S#, CITY)Prof.Dr.S.Sridhar and CS (CITY, STATUS) Database Security & Back-ups Security problems : Legal, Social and ethical aspects Eg : does a person making the request say for customer’s credit - have a legal right to the requested information. Physical Controls Eg : Computer or Terminal room locked ! Or otherwise guarded ? ! Policy Questions Who should have access ? ! To what level ? ! Operational Problems - Password is used. Then - How are the passwords Rept secrete ? - How often they are changed ? Lecture by Prof.Dr.S.Sridhar 29 Hardware Control - Whether the processing unit has any security measures,such as storage protection keys or privileged operation mode ? Operating System Security - Whether the O /S erase the contents of storage ? ! and Data files ? ! Issues Concerned with DBMS - Whether the DBMS has the concept of ownership ? Examples of Discretionary Access control Create Security Rule SR3 GRANT RETRIEVE (S#, SNAME, CITY), DELETE ON S WHERE S.CITY = ‘LONDON’ TO JIM, FRED, MARY ON ATTEMPTED VIOLATION REJECT; Lecture by Prof.Dr.S.Sridhar 30 CREATE SECURITY RULE EX1: GRANT RETRIVE (S#, SNAME, CITY) ON S TO JAC, ANNE, CHARLEY; CREATE SECURITY RULE EX4: GRANT RETRIVE (S#, SNAME) ON S WHERE S. STATUS > 50 TO JAC, PAVL; CREATE VIEW SSS AS (S.S#, S.NAME) WHERE S.STATUS > 50; Request Modification : DEFINE PERMIT RETRIEVE ON P TO U WHERE P.CITY = “London” RETRIEVE (P.P#, P.WEIGHT) WHERE P.COLOR = “Red” RETRIEVE (P.P#, P.WEIGHT) WHERE P.CITY = “London” Lecture by Prof.Dr.S.Sridhar 31 Back – ups: Daily Back-up After the day work of all transactions, copying the entire system files / data files on a floppy, disk (CD), or a tape depending on the size of total volume. Weekly back-up Monthly back-up Yearly back-up In DBMS – Mirroring concept as back –up. Stand-by server as back-up CD- Server as back –up Software, specially in Oracle like RDBMS, auto back-up and retrieval systems. Lecture by Prof.Dr.S.Sridhar 32 SQL – Language CREATE TABLE ( S# S# NOT NULL, P# P# NOT NULL, QTY QTY NOT NULL, PRIMARY KEY (S#) REFERENCE S ON DELETE CASECADE ON UPDATE CASCADE, CHECK(QTY > 0 AND QTY < 5001)); This is to create a table with attributes S#, P#, QTY, all NOT NULL values and the primary key is S# also, check the condition while creating the table itself whether QTY is > 0 and QTY < 5001. Here CASCADE is used as an Option. ALTER TABLE S ADD COLUMN DISCOUNT INTEGER DEFAULT – 1; Here we can alter the table S by adding a new column namely DISCOUNT which is an integer quantity with default value = -1. Lecture by Prof.Dr.S.Sridhar 33 DROP TABLE S; This will drop the created table S from the database. Data Manipulation (Retrieval operations) (called DML) SELECT P.COLOR, P.CITY FROM P WHERE P.CITY < >’PARIS’ AND P.WEIGHT > 10; ------------------------------------------------------------------SELECT DISTINCT P.COLOR, P.CITY FROM P WHERE P.CITY < > ‘PARIS’ AND P.WEIGHT > 10; -----------------------------------------------------------------SELECT P.COLOR, P.CITY FROM P WHERE P.CITY < >’PARIS’ AND P.WEIGHT > 10; ORDER BY CITY DESC; Lecture by Prof.Dr.S.Sridhar 34 SELECT P.P#, P.WEIGHT * 454 AS GMNT From P; ------------------------------------------------------------------SELECT * FROM S; ------------------------------------------------------------------SELECT S.S#, S.NAME, S.STATUS, S.CITY, P.P#, P.NAME, P.COLOR, P. WEIGHT, FROM S, P WHERE S.CITY = P.CITY; ----------------------------------------------------------------S JOIN P USING CITY -----------------------------------------------------------------SELECT DISTINCT S.CITY AS SCITY, P. CITY AS PCITY FROM S JOIN SP USING S# JOIN P USING P#; ------------------------------------------------------------------SELECT COUNT(*) AS NFROM S; ------------------------------------------------------------------SELECT MAX (SP.QTY)AS MAXQ, MIN (SP.QTY)AS MINQ FROM SP WHERE SP.P# = ‘P2’; Lecture by Prof.Dr.S.Sridhar 35 Update Operations INSERT INTO P (P#, PNAME, COLOR, CITY) VALUES (‘P8’, ‘Sprocket’,’pink’, ‘Nice’); ------------------------------------------------------------------INSERT INTO TEMP(S#, CITY) SELECT S.S#, S.CITY FROM S WHERE S.STATUS > 15; ------------------------------------------------------------------DELETE FROM SP WHERE ‘London’ = (SELECT S.CITY FROM S WHERE S.S# = SP. S#); ------------------------------------------------------------------All or Any conditions SELECT DISTINCT PX.NAME FROM P AS PX WHERE PX.WEIGHT > ALL ( SELECT PY.WEIGHT FROM P AS PY WHERE PY.COLOR = ‘Blue’); Lecture by Prof.Dr.S.Sridhar 36 1) Exercises ON SQL CREATE TABLE ( S# S# NOT NULL, P# P# NOT NULL, QTY QTY NOT NULL) Ans S# P# QTY 4532 4533 4534 4535 123 234 345 567 10 20 40 5 2) CREATE TABLE ( S# S# NOT NULL, P# P# NOT NULL, QTY QTY NOT NULL) PRIMARY KEY (S#) REFERENCE S CHECK (QTY >0 NAD QTY <= 20)); Ans S# P# QTY 4532 4533 4535 123 234 567 10 20 5 Lecture by Prof.Dr.S.Sridhar 37 3) ALTER TABLE S ADD COLUMN DISCOUNT INTEGER DEFAULT –1; Ans S# P# Qty Discount 4532 4533 4535 123 234 567 10 20 5 -1 10 5 4) DROP TABLE S; ( Then the table S will NOT be in DB) DATA MANIPULATION 1) SELECT S.P#, S.QTY FROM S WHERE S.QTY < > 20 AND S.P# > 500 ; Ans S# P# QTY 4535 567 5 2) SELECT DISTINCT S.S#, S.P#, S.QTY FROM S WHERE S.QTY < > 20 AND S,P# > 500; Ans S# P# QTY 4535 567 Lecture by Prof.Dr.S.Sridhar 5 38 3) SELECT DISTINCT S.S#, S.P#, S.QTY S. Discount FROM S WHERE S.QTY < 5 AND S,P# > 125 ORDER BY S# DESC; Ans S# P# QTY Discount 4533 234 20 10 4) SELECT S.S#, S.P#, S.QTY * 10 AS QTY2 FROM S WHERE DISCOUNT > 0; Ans S# P# QTY QTY2 4533 234 4535 567 20 5 5) SELECT * FROM S; Ans S# P# 4532 4533 4535 200 50 Qty 123 10 234 20 Lecture by 567 5 Prof.Dr.S.Sridhar Discount -1 10 5 39 6) SELECT S.S#, S.P#, S.QTY, P. P#, P.NAME FROM S, P WHERE S.P# = P.P# ; S Ans ( input) S# P# Qty Discount 4532 4533 4534 4535 123 234 345 567 10 20 40 5 -1 10 -1 5 P# PNAME 4532 4555 4535 4534 NUT BOLT SCREEN ROD Answer S.S# S.P# S.QTY 4532 4534 4535 7) 123 345 567 10 40 5 P# PNAME 4532 4534 4535 NUT ROD SCREEN S JOIN P USING P# Answer is the same as 6. .X. in this case only Lecture by Prof.Dr.S.Sridhar 40 8) SELECT COUNT ( * ) AS N FORM S; Ans Count = N = 4 9) SELECT MAX ( S.QTY) AS MAXQ, MIN (S.QTY) AS MINQ FROM S WHERE S. DISCOUNT = -1; Answer MAXQ = 40 MINQ = 10 10) UPDATE Operations: INSERT INTO P ( P#, PNMAE) VALUES (4536, ‘STRING’); P# PNAME 4532 4555 4535 4534 4536 NUT BOLT SCREEN ROD STRING ? Ans DELETE FROM P WHERE P.PNAME = ‘ROD’; P# PNAME 4532 NUT 4555 BOLT 4535 Lecture SCREEN by 4536 Prof.Dr.S.Sridhar STRING 41