1 / 80

DBMS Fundamentals

DBMS Fundamentals. Dr. E.Grace Mary Kanaga Associate Professor / CSE Karunya University. Agenda. Introduction DDL DML DCL TCL ER Design Normalization. Introduction. Data Facts and statistics collected together for reference or analysis Database

hu-bowers
Download Presentation

DBMS Fundamentals

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. DBMS Fundamentals Dr. E.Grace Mary Kanaga Associate Professor / CSE Karunya University Database Management Systems - Placement Training

  2. Agenda • Introduction • DDL • DML • DCL • TCL • ER Design • Normalization Database Management Systems - Placement Training

  3. Introduction Data Facts and statistics collected together for reference or analysis Database It is an organized collection of data  that is organized so that it can easily be accessed, managed, and updated Database Systems Database system is a system to achieve an organized, store a large number of dynamical associated data, facilitate for multi-user accessing to computer hardware, software and data, that it is a computer system with database technology Database Management Systems - Placement Training

  4. Introduction Database Management Systems They are specially designed software applications that interact with the user, other applications, and the database itself to capture and analyse data. A general-purpose DBMS is a software system designed to allow the definition, creation, querying, update, and administration of databases. Data Model It is a specification describing how a database is structured and used Database Management Systems - Placement Training

  5. Data Model - Types Flat model: This may not strictly qualify as a data model.. Hierarchical model: In this model data is organized into a tree-like structure Network model: This model organizes data using two fundamental constructs, called records and sets. Relational model: is a database model based on first-order predicate logic. Database Management Systems - Placement Training

  6. Database Management Systems • MySQL • MariaDB • PostgreSQL • SQLite •  Microsoft SQL Server •  Microsoft Access • Oracle •  IBM DB2 • SAP • HANA • dBASE, • LibreOffice Base •  FileMaker Pro • InterSystemsCaché •  FoxPro Database Management Systems - Placement Training

  7. Database Objects Object Description Table Basic unit of storage; composed of rows and columns View Logically represents subsets of data from one or more tables Sequence Numeric value generator Index Improves the performance of some queries Synonym Gives alternative names to objects Database Management Systems - Placement Training

  8. DDL- Data Definition Language Statement Description CREATE TABLE Creates a table ALTER TABLE Modifies table structures DROP TABLE Removes the rows and table structure RENAME Changes the name of a table, view, sequence, or synonym TRUNCATE Removes all rows from a table and releases the storage space COMMENT Adds comments to a table or view Database Management Systems - Placement Training

  9. Naming Rules Table names and column names: • Must begin with a letter • Must be 1–30 characters long • Must contain only A–Z, a–z, 0–9, _, $, and # • Must not duplicate the name of another object owned by the same user • Must not be an Oracle server reserved word Database Management Systems - Placement Training

  10. The CREATE TABLE Statement • You must have: • CREATE TABLE privilege • A storage area • You specify: • Table name • Column name, column data type, and column size CREATE TABLE [schema.]table (columndatatype [DEFAULT expr][, ...]); Database Management Systems - Placement Training

  11. Tables in the Oracle Database • User Tables: • Are a collection of tables created and maintained by the user • Contain user information • Data Dictionary: • Is a collection of tables created and maintained by the Oracle Server • Contain database information Database Management Systems - Placement Training

  12. Data Types Data Type Description VARCHAR2(size) Variable-length character data CHAR(size) Fixed-length character data NUMBER(p,s) Variable-length numeric data DATE Date and time values LONG Variable-length character data up to 2 gigabytes CLOB Character data up to 4 gigabytes RAW and LONG RAW Raw binary data BLOB Binary data up to 4 gigabytes BFILE Binary data stored in an external file; up to 4 gigabytes ROWID A 64 base number system representing the unique address of a row in its table. Database Management Systems - Placement Training

  13. DML COMMANDS Database Management Systems - Placement Training • The acronym DML represents Data Manipulation Language • Data Manipulation Language (DML) statements are used to define the data in the database • The Data Manipulation Language (DML) is used to retrieve, insert and modify database information.  • These commands will be used by all database users during the routine operation of the database • INSERT • SELECT • UPDATE • DELETE

  14. DML: Insert The INSERT command in SQL is used to add records to an existing table.  Syntax: INSERT INTO table name(field name1 data type, field name2 data type ……. field name n data type); EXAMPLE SQL> insert into stud1(st_name,st_num,st_dob,sub1,sub2,tot) values ('&st_name',&st_num,&st_dob,&sub1,&sub2,&tot); SQL> / Enter value for st_name: charles Enter value for st_num: 1 Enter value for st_dob: '20 may 2000' Enter value for sub1: 90 Enter value for sub2: 80 Enter value for tot: 170 Database Management Systems - Placement Training

  15. Update The UPDATE command can be used to modify information contained within a table, either in bulk or individually. Syntax 1.UPDATE tablename SET fieldname=new value; 2. UPDATE table name SET fieldname=new value where condition; EXAMPLE update emp1 set emp_sal = emp_sal + 500 where emp_bpay > 5000; Database Management Systems - Placement Training

  16. DELETE : To delete data from the database. DELETE [FROM] table [WHERE condition]; SELECT: To retrieve the data from the database. SELECT *|{[DISTINCT] column|expression[alias],...} FROM table; SELECT *|{[DISTINCT] column|expression[alias],...} FROM table [WHERE condition(s)]; A where clause is used to restrict the rows returned. Database Management Systems - Placement Training

  17. The select Clause Database Management Systems - Placement Training • The select clause list the attributes desired in the result of a query • corresponds to the projection operation of the relational algebra • Example: find the names of all branches in the loan relation:select branch_namefrom loan • In the relational algebra, the query would be: branch_name (loan) • NOTE: SQL names are case insensitive (i.e., you may use upper- or lower-case letters.) • E.g. Branch_Name ≡ BRANCH_NAME ≡ branch_name • Some people use upper case wherever we use bold font.

  18. The select Clause (Cont.) Database Management Systems - Placement Training SQL allows duplicates in relations as well as in query results. To force the elimination of duplicates, insert the keyword distinct after select. Find the names of all branches in the loan relations, and remove duplicates select distinct branch_namefrom loan The keyword all specifies that duplicates not be removed. select allbranch_namefrom loan

  19. The select Clause (Cont.) Database Management Systems - Placement Training An asterisk in the select clause denotes “all attributes” select *from loan The select clause can contain arithmetic expressions involving the operation, +, –, , and /, and operating on constants or attributes of tuples. E.g.: selectloan_number, branch_name, from loan

  20. The where Clause Database Management Systems - Placement Training • The whereclause specifies conditions that the result must satisfy • Corresponds to the selection predicate of the relational algebra. • To find all loan number for loans made at the Perryridge branch with loan amounts greater than $1200. select loan_numberfrom loanwhere branch_name ='Perryridge'and amount > 1200 • Comparison results can be combined using the logical connectives and, or, and not.

  21. The from Clause • Find the name, loan number and loan amount of all customers having a loan at the Perryridge branch. select customer_name, borrower.loan_number, amountfrom borrower, loanwhere borrower.loan_number = loan.loan_number andbranch_name = 'Perryridge' Database Management Systems - Placement Training • The fromclause lists the relations involved in the query • Corresponds to the Cartesian product operation of the relational algebra. • Find the Cartesian product borrower X loan select from borrower, loan

  22. The Rename Operation select customer_name, borrower.loan_number as loan_id, amountfrom borrower, loanwhere borrower.loan_number = loan.loan_number Database Management Systems - Placement Training SQL allows renaming relations and attributes using the as clause: old-name as new-name E.g. Find the name, loan number and loan amount of all customers; rename the column name loan_number as loan_id.

  23. String Operations Database Management Systems - Placement Training • SQL includes a string-matching operator for comparisons on character strings. The operator “like” uses patterns that are described using two special characters: • percent (%). The % character matches any substring. • underscore (_). The _ character matches any character. • Find the names of all customers whose street includes the substring “Main”. select customer_namefrom customerwherecustomer_streetlike '% Main%' • Match the name “Main%” like 'Main\%' escape '\' • SQL supports a variety of string operations such as • concatenation (using “||”) • converting from upper to lower case (and vice versa) • finding string length, extracting substrings, etc.

  24. Ordering the Display of Tuples Database Management Systems - Placement Training • List in alphabetic order the names of all customers having a loan in Perryridge branch select distinct customer_namefrom borrower, loanwhere borrower loan_number = loan.loan_number and branch_name = 'Perryridge' order by customer_name • We may specify desc for descending order or asc for ascending order, for each attribute; ascending order is the default. • Example: order bycustomer_namedesc

  25. Set Operations (selectcustomer_name from depositor)union(selectcustomer_name from borrower) • Find all customers who have both a loan and an account. (selectcustomer_name from depositor)intersect(selectcustomer_name from borrower) • Find all customers who have an account but no loan. (selectcustomer_name from depositor)except(selectcustomer_name from borrower) Database Management Systems - Placement Training Find all customers who have a loan, an account, or both:

  26. DCL • Grant • Revoke Database Management Systems - Placement Training

  27. TCL • Save Point • Roll Back • Commit Database Management Systems - Placement Training

  28. Normalization • This is the process which allows you to winnow out redundant data within your database. • This involves restructuring the tables to successively meeting higher forms of Normalization. • A properly normalized database should have the following characteristics • Scalar values in each fields • Absence of redundancy. • Minimal use of null values. • Minimal loss of information. Database Management Systems - Placement Training

  29. Levels of Normalization • Levels of normalization based on the amount of redundancy in the database. • Various levels of normalization are: • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • Boyce-Codd Normal Form (BCNF) • Fourth Normal Form (4NF) • Fifth Normal Form (5NF) • Domain Key Normal Form (DKNF) Redundancy Number of Tables Complexity Most databases should be 3NF or BCNF in order to avoid the database anomalies. Database Management Systems - Placement Training

  30. 1NF 2NF 3NF 4NF 5NF DKNF Levels of Normalization Each higher level is a subset of the lower level Database Management Systems - Placement Training

  31. 0-321-32132-1 Balloon Sleepy, Snoopy, Grumpy 321-321-1111, 232-234-1234, 665-235-6532 Small House 714-000-0000 $34.00 0-55-123456-9 Main Street Jones, Smith 123-333-3333, 654-223-3455 Small House 714-000-0000 $22.95 0-123-45678-0 Ulysses Joyce 666-666-6666 Alpha Press 999-999-9999 $34.00 1-22-233700-0 ISBN Title Visual Basic Roman AuName AuPhone 444-444-4444 PubName Big House 123-456-7890 PubPhone $25.00 Price First Normal Form (1NF) A table is considered to be in 1NF if all the fields contain only scalar values (as opposed to list of values). Example (Not 1NF) Author and AuPhone columns are not scalar Database Management Systems - Placement Training

  32. 0-321-32132-1 0-321-32132-1 0-321-32132-1 0-321-32132-1 Balloon Grumpy Snoopy Sleepy 321-321-1111 232-234-1234 665-235-6532 Small House 714-000-0000 $34.00 0-55-123456-9 0-55-123456-9 0-55-123456-9 Main Street Jones Smith 654-223-3455 123-333-3333 Small House 714-000-0000 $22.95 0-123-45678-0 0-123-45678-0 Ulysses Joyce 666-666-6666 Alpha Press 999-999-9999 $34.00 ISBN ISBN 1-22-233700-0 1-22-233700-0 Visual Basic Title Roman AuName 444-444-4444 AuPhone PubName Big House 123-456-7890 PubPhone $25.00 Price 1NF - Decomposition • Place all items that appear in the repeating group in a new table • Designate a primary key for each new table produced. • Duplicate in the new table the primary key of the table from which the repeating group was extracted or vice versa. Example (1NF) Database Management Systems - Placement Training

  33. 0-321-32132-1 Balloon $34.00 0-55-123456-9 Main Street $22.95 0-123-45678-0 Ulysses $34.00 ISBN 1-22-233700-0 Visual Basic Title Price $25.00 Functional Dependencies • If one set of attributes in a table determines another set of attributes in the table, then the second set of attributes is said to be functionally dependent on the first set of attributes. Example 1 Table Scheme: {ISBN, Title, Price} Functional Dependencies: {ISBN}  {Title} {ISBN}  {Price} Database Management Systems - Placement Training

  34. 1 1 3 2 Big House Sleepy Grumpy Snoopy 321-321-1111 232-234-1234 665-235-6532 999-999-9999 5 2 4 Small House Jones Smith 123-333-3333 654-223-3455 123-456-7890 6 3 Alpha Press Joyce 666-666-6666 111-111-1111 AuID PubID 7 PubName AuName Roman 444-444-4444 AuPhone PubPhone Functional Dependencies Table Scheme: {PubID, PubName, PubPhone} Functional Dependencies: {PubId}  {PubPhone} {PubId}  {PubName} {PubName, PubPhone}  {PubID} Example 2 Example 3 Table Scheme: {AuID, AuName, AuPhone} Functional Dependencies: {AuId}  {AuPhone} {AuId}  {AuName} {AuName, AuPhone}  {AuID} Database Management Systems - Placement Training

  35. FD – Example Database to track reviews of papers submitted to an academic conference. Prospective authors submit papers for review and possible acceptance in the published conference proceedings. Details of the entities • Author information includes a unique author number, a name, a mailing address, and a unique (optional) email address. • Paper information includes the primary author, the paper number, the title, the abstract, and review status (pending, accepted,rejected) • Reviewer information includes the reviewer number, the name, the mailing address, and a unique (optional) email address • A completed review includes the reviewer number, the date, the paper number, comments to the authors, comments to the program chairperson, and ratings (overall, originality, correctness, style, clarity) Database Management Systems - Placement Training

  36. FD – Example Functional Dependencies • AuthNo  AuthName, AuthEmail, AuthAddress • AuthEmail  AuthNo • PaperNo  Primary-AuthNo, Title, Abstract, Status • RevNo  RevName, RevEmail, RevAddress • RevEmail  RevNo • RevNo, PaperNo  AuthComm, Prog-Comm, Date, Rating1, Rating2, Rating3, Rating4, Rating5 Database Management Systems - Placement Training

  37. Second Normal Form (2NF) For a table to be in 2NF, there are two requirements • The database is in first normal form • All nonkey attributes in the table must be functionally dependent on the entire primary key Note: Remember that we are dealing with non-key attributes Example 1 (Not 2NF) Scheme  {Title, PubId, AuId, Price, AuAddress} • Key  {Title, PubId, AuId} • {Title, PubId, AuID}  {Price} • {AuID}  {AuAddress} • AuAddress does not belong to a key • AuAddress functionally depends on AuId which is a subset of a key Database Management Systems - Placement Training

  38. Second Normal Form (2NF) Example 2 (Not 2NF) Scheme  {City, Street, HouseNumber, HouseColor, CityPopulation} • key  {City, Street, HouseNumber} • {City, Street, HouseNumber}  {HouseColor} • {City}  {CityPopulation} • CityPopulation does not belong to any key. • CityPopulation is functionally dependent on the City which is a proper subset of the key Example 3 (Not 2NF) Scheme  {studio, movie, budget, studio_city} • Key  {studio, movie} • {studio, movie}  {budget} • {studio}  {studio_city} • studio_city is not a part of a key • studio_city functionally depends on studio which is a proper subset of the key Database Management Systems - Placement Training

  39. 2NF - Decomposition • If a data item is fully functionally dependent on only a part of the primary key, move that data item and that part of the primary key to a new table. • If other data items are functionally dependent on the same part of the key, place them in the new table also • Make the partial primary key copied from the original table the primary key for the new table. Place all items that appear in the repeating group in a new table Example 1 (Convert to 2NF) Old Scheme  {Title, PubId, AuId, Price, AuAddress} New Scheme  {Title, PubId, AuId, Price} New Scheme  {AuId, AuAddress} Database Management Systems - Placement Training

  40. 2NF - Decomposition Example 2 (Convert to 2NF) Old Scheme  {Studio, Movie, Budget, StudioCity} New Scheme  {Movie, Studio, Budget} New Scheme  {Studio, City} Example 3 (Convert to 2NF) Old Scheme  {City, Street, HouseNumber, HouseColor, CityPopulation} New Scheme  {City, Street, HouseNumber, HouseColor} New Scheme  {City, CityPopulation} Database Management Systems - Placement Training

  41. Third Normal Form (3NF) This form dictates that all non-key attributes of a table must be functionally dependent on a candidate key i.e. there can be no interdependencies among non-key attributes. For a table to be in 3NF, there are two requirements • The table should be second normal form • No attribute is transitively dependent on the primary key Example (Not in 3NF) Scheme  {Title, PubID, PageCount, Price } • Key  {Title, PubId} • {Title, PubId}  {PageCount} • {PageCount}  {Price} • Both Price and PageCount depend on a key hence 2NF • Transitively {Title, PubID}  {Price} hence not in 3NF Database Management Systems - Placement Training

  42. BuildingID Contractor Fee 100 Randolph 1200 150 Ingersoll 1100 200 Randolph 1200 250 Pitkin 1100 300 Randolph 1200 Third Normal Form (3NF) Example 2 (Not in 3NF) Scheme  {Studio, StudioCity, CityTemp} • Primary Key  {Studio} • {Studio}  {StudioCity} • {StudioCity}  {CityTemp} • {Studio}  {CityTemp} • Both StudioCity and CityTemp depend on the entire key hence 2NF • CityTemp transitively depends on Studio hence violates 3NF Example 3 (Not in 3NF) Scheme  {BuildingID, Contractor, Fee} • Primary Key  {BuildingID} • {BuildingID}  {Contractor} • {Contractor}  {Fee} • {BuildingID}  {Fee} • Fee transitively depends on the BuildingID • Both Contractor and Fee depend on the entire key hence 2NF Database Management Systems - Placement Training

  43. 3NF - Decomposition • Move all items involved in transitive dependencies to a new entity. • Identify a primary key for the new entity. • Place the primary key for the new entity as a foreign key on the original entity. Example 1 (Convert to 3NF) Old Scheme  {Title, PubID, PageCount, Price } New Scheme  {PubID, PageCount, Price} New Scheme  {Title, PubID, PageCount} Database Management Systems - Placement Training

  44. BuildingID Contractor Contractor Fee 100 Randolph Randolph 1200 150 Ingersoll Ingersoll 1100 200 Randolph 250 Pitkin Pitkin 1100 300 Randolph 3NF - Decomposition Example 2 (Convert to 3NF) Old Scheme  {Studio, StudioCity, CityTemp} New Scheme  {Studio, StudioCity} New Scheme  {StudioCity, CityTemp} Example 3 (Convert to 3NF) Old Scheme  {BuildingID, Contractor, Fee} New Scheme  {BuildingID, Contractor} New Scheme  {Contractor, Fee} Database Management Systems - Placement Training

  45. Boyce-Codd Normal Form (BCNF) • BCNF does not allow dependencies between attributes that belong to candidate keys. • BCNF is a refinement of the third normal form in which it drops the restriction of a non-key attribute from the 3rd normal form. • Third normal form and BCNF are not same if the following conditions are true: • The table has two or more candidate keys • At least two of the candidate keys are composed of more than one attribute • The keys are not disjoint i.e. The composite candidate keys share some attributes Example 1 - Address (Not in BCNF) Scheme  {City, Street, ZipCode } • Key1  {City, Street } • Key2  {ZipCode, Street} • No non-key attribute hence 3NF • {City, Street}  {ZipCode} • {ZipCode}  {City} • Dependency between attributes belonging to a key Database Management Systems - Placement Training

  46. Boyce Codd Normal Form (BCNF) Example 2 - Movie (Not in BCNF) Scheme  {MovieTitle, MovieID, PersonName, Role, Payment } • Key1  {MovieTitle, PersonName} • Key2  {MovieID, PersonName} • Both role and payment functionally depend on both candidate keys thus 3NF • {MovieID}  {MovieTitle} • Dependency between MovieID & MovieTitle Violates BCNF Example 3 - Consulting (Not in BCNF) Scheme  {Client, Problem, Consultant} • Key1  {Client, Problem} • Key2  {Client, Consultant} • No non-key attribute hence 3NF • {Client, Problem}  {Consultant} • {Client, Consultant}  {Problem} • Dependency between attributess belonging to keys violates BCNF Database Management Systems - Placement Training

  47. BCNF - Decomposition • Place the two candidate primary keys in separate entities • Place each of the remaining data items in one of the resulting entities according to its dependency on the primary key. Example 1 (Convert to BCNF) Old Scheme  {City, Street, ZipCode } New Scheme1  {ZipCode, Street} New Scheme2  {City, Street} • Loss of relation {ZipCode}  {City} Alternate New Scheme1  {ZipCode, Street } Alternate New Scheme2  {ZipCode, City} Database Management Systems - Placement Training

  48. Decomposition – Loss of Information • If decomposition does not cause any loss of information it is called a lossless decomposition. • If a decomposition does not cause any dependencies to be lost it is called a dependency-preserving decomposition. • Any table scheme can be decomposed in a lossless way into a collection of smaller schemas that are in BCNF form. However the dependency preservation is not guaranteed. • Any table can be decomposed in a lossless way into 3rd normal form that also preserves the dependencies. • 3NF may be better than BCNF in some cases Use your own judgment when decomposing schemas Database Management Systems - Placement Training

  49. BCNF - Decomposition Example 2 (Convert to BCNF) Old Scheme  {MovieTitle, MovieID, PersonName, Role, Payment } New Scheme  {MovieID, PersonName, Role, Payment} New Scheme  {MovieTitle, PersonName} • Loss of relation {MovieID}  {MovieTitle} New Scheme  {MovieID, PersonName, Role, Payment} New Scheme  {MovieID, MovieTitle} • We got the {MovieID}  {MovieTitle} relationship back Example 3 (Convert to BCNF) Old Scheme  {Client, Problem, Consultant} New Scheme  {Client, Consultant} New Scheme  {Client, Problem} Database Management Systems - Placement Training

  50. Movie ScreeningCity Genre Hard Code Los Angles Comedy Hard Code New York Comedy Bill Durham Santa Cruz Drama Bill Durham Durham Drama The Code Warrier New York Horror Fourth Normal Form (4NF) • Fourth normal form eliminates independent many-to-one relationships between columns. • To be in Fourth Normal Form, • a relation must first be in Boyce-Codd Normal Form.  • a given relation may not contain more than one multi-valued attribute. Example (Not in 4NF) Scheme  {MovieName, ScreeningCity, Genre) Primary Key: {MovieName, ScreeningCity, Genre) • All columns are a part of the only candidate key, hence BCNF • Many Movies can have the same Genre • Many Cities can have the same movie • Violates 4NF Database Management Systems - Placement Training

More Related