1 / 41

DataBase Technology by

DataBase Technology by. Dr.S.Sridhar, Ph.D.(JNUD), RACI(Paris, NICE), RMR(USA), RZFM(Germany) DIRECTOR ARUNAI ENGINEERING COLLEGE TIRUVANNAMALAI . DBMS CONCEPTS Data Base Management Systems. Examples of Databases Files & DB – Systems Advantages of DBMS

love
Télécharger la présentation

DataBase Technology by

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 Technologyby Dr.S.Sridhar, Ph.D.(JNUD),RACI(Paris, NICE), RMR(USA), RZFM(Germany)DIRECTORARUNAI ENGINEERING COLLEGETIRUVANNAMALAI Lecture by Prof.Dr.S.Sridhar

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

  3. 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 offields. Lecture by Prof.Dr.S.Sridhar

  4. The above file is referred as ‘Table’ in RDBMS – Sense. • Call this table as “CELLAR” • This contains many records. • Each record has fields. • Here • is a record • The values inside the table are called “ Field values” Lecture by Prof.Dr.S.Sridhar Fields

  5. Retrieve Data (Query Language) SELECT WINE, BIN, PRODUCER FROM CELLAR WHERE READY = 1995 ; RESULT 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 Prof.Dr.S.Sridhar

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

  7. Data Base (DBMS) Data Base Management System Application END Programs USERS Major Components: * Data * Hardware Simplified View * Software of Data base System * Users An Overview of Database Management Lecture by Prof.Dr.S.Sridhar

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

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

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

  11. 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) Persistent Data (Egs) ************ **************** • A Manufacturing * Product Data Company • A Bank * Account Data • A Hospital * Patient Data • A University • A govt. Dept. Lecture by Prof.Dr.S.Sridhar

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

  13. 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 EMP Lecture by Prof.Dr.S.Sridhar

  14. 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 (Specified Rows) PROJECT DEPTs OVER DEPT#, BUDGET Result (Specified Columns) JOIN DEPTs and EMPs OVER DEPT# Result Common Rows Common Columns Lecture by Prof.Dr.S.Sridhar

  15. Entity – Attributes –Relationship models Concept InformationExamples Definition ENTITY A Distinguishable - Supplier object - Part - Person - Purchase Order and so on PROPERTY A piece of information - Supplier number to describe an entity - Shipment Qty - Person height - P.O.data etc. RELATIONSHIP An entity that serves to - Shipment interconnect 2 or more (Supplier-part) entities - Assignment (Emp-Dept.) Here Note 1) Shipment is RELATIONSHIP between supplier and part. R R Similarly for Assignment. Supplier Part Lecture by Prof.Dr.S.Sridhar

  16. Department Dept-Emp Emp Employee Entity / Relationship diagram (Example) -----*---------*----------*--------*---------* Entities : Department, Employee, Dependent Relations : Dept-Emp, Emp-dep Attributes : ( to employee) Emp#, Ename,Salary First MN Last (Group item) Ename Emp-Dep First MN Last Dependent Salary Lecture by Prof.Dr.S.Sridhar

  17. 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. Key Lecture by Prof.Dr.S.Sridhar

  18. 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 (1-1) In an organization. (1-M) R ; . ; (M-1) R : : ; (M – M) Emp Seat Customer1 Supplier Customer-m Student1 Teacher Student-m Suplier 1…….m Customer1 1………m Lecture by Prof.Dr.S.Sridhar

  19. Book Student Many Examples of Attributes / Entities & Relations Example 1: Book –Student BOOK-STD R > R R< STD -BOOK Entities : Book, Student Relation : BOOK-STD and STD-BOOK Attributes of Book Attributes of Student • Author  RollNo • Title  ExamNo • Publisher  Name • Year  Class • ISBN Number  Address • Number of pages  Phone No • Price  Remarks • Printer • Location of printing • Edition-Nature • Remarks Lecture by Prof.Dr.S.Sridhar

  20. Part Supplier Example 2 ( R ) Part-Sup ( R ) Sub-part ( R ) Entities : Part, Supplier Relations : Part-Sup and Sub-part Attributes of partAttributes of Supplier • PartNo  Supplier code • Description  Supplier address • Qty  Supplier phone • Unit price  Supplier location • Opening balance  Delivery time • Parts-new  Address demand • Net balance  Warranty Period • Remarks  Remarks Lecture by Prof.Dr.S.Sridhar

  21. Student Faculty Example 3: Stud-fac R R R Fac-stud Entities : Student and Faculty Relations : Stud-fac and Fac-stud Attributes of Student Attributes of Faculty • RollNo  Facuty ID • ExamNo  Name • Name  Classes Handling • Class  Qualification • Address  Designation • PhoneNo  Address • Faculty Division  Phone No • Remarks  Students Related for Advising Lecture by Prof.Dr.S.Sridhar

  22. 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 Lecture by Prof.Dr.S.Sridhar

  23. 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 CITY S# QTY P# STATUS Lecture by Prof.Dr.S.Sridhar

  24. Sample Tabulation of FIRST Now problems, We face with • INSERT • DELETE • UPDATE Lecture by Prof.Dr.S.Sridhar

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

  26. CITY 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) Functional Dependencies in relation SECOND AND SP SECOND SAMPLE TABULATION OF SECOND AND SP S# QTY S# P# STATUS Lecture by Prof.Dr.S.Sridhar

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

  28. Example: Key Key Sample Tabulation of SC and CS The above relation SC and CS are in 3NF. Whereas, SECOND is not in 3NF. SECOND Thus SECOND (S#, STATUS, CITY) is tabulated in 3NF as SC(S#, CITY) and CS (CITY, STATUS) Lecture by Prof.Dr.S.Sridhar

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

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

  31.  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

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

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

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

  35. 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 FROMS JOIN SP USING S# JOIN P USING P#; ------------------------------------------------------------------- SELECT COUNT(*) AS NFROM S; ------------------------------------------------------------------- SELECTMAX (SP.QTY)AS MAXQ, MIN (SP.QTY)AS MINQ FROM SP WHERE SP.P# = ‘P2’; Lecture by Prof.Dr.S.Sridhar

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

  37. Exercises ON SQL • CREATE TABLE ( S# S# NOT NULL, P# P# NOT NULL, QTY QTY NOT NULL) Ans 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 Lecture by Prof.Dr.S.Sridhar

  38. 3) ALTER TABLE S ADD COLUMN DISCOUNT INTEGER DEFAULT –1; Ans 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 2) SELECT DISTINCT S.S#, S.P#, S.QTY FROM S WHERE S.QTY < > 20 AND S,P# > 500; Ans Lecture by Prof.Dr.S.Sridhar

  39. 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 4) SELECT S.S#, S.P#, S.QTY * 10 AS QTY2 FROM S WHERE DISCOUNT > 0; Ans 5) SELECT * FROM S; Ans Lecture by Prof.Dr.S.Sridhar

  40. 6) SELECT S.S#, S.P#, S.QTY, P. P#, P.NAME FROM S, P WHERE S.P# = P.P# ; S Ans ( input)       Answer • S JOIN P USING P# Answer is the same as 6. .X. in this case only Lecture by Prof.Dr.S.Sridhar

  41. 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’); ? Ans  DELETE FROM P WHERE P.PNAME = ‘ROD’; Lecture by Prof.Dr.S.Sridhar

More Related