Download - DISE - Database Concepts
![Page 1: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/1.jpg)
Diploma in Software Engineering
Module IV: Database Concepts
Rasan SamarasingheESOFT Computer Studies (pvt) Ltd.No 68/1, Main Street, Pallegama, Embilipitiya.
![Page 2: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/2.jpg)
Contents1. Introduction to Databases2. Data3. Information4. Database5. Database System6. Database Applications7. Evolution of Databases8. Traditional Files Based Systems9. Limitations in Traditional Files10. The Database Approach11. Advantages of Database Approach12. Disadvantages of Database Approach13. Database Management Systems14. DBMS Functions15. Database Architecture16. ANSI-SPARC 3 Level Architecture17. The Relational Data Model18. What is a Relation?19. Primary Key20. Cardinality and Degree21. Relationships22. Foreign Key23. Data Integrity 24. Data Dictionary
25. Database Design26. Requirements Collection and analysis27. Conceptual Design28. Logical Design29. Physical Design30. Entity Relationship Model31. A mini-world example32. Entities33. Relationships34. ERD Notations35. Cardinality36. Optional Participation37. Entities and Relationships38. Attributes39. Entity Relationship Diagram40. Entities41. ERD Showing Weak Entities42. Super Type / Sub Type Relationships43. Mapping ERD to Relational44. Map Regular Entities45. Map Weak Entities46. Map Binary Relationships47. Map Associated Entities48. Map Unary Relationships
![Page 3: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/3.jpg)
Content49. Map Ternary Relationships50. Map Supertype/Subtype Relationships51. Normalization52. Advantages of Normalization53. Disadvantages of Normalization54. Normal Forms55. Functional Dependency 56. Purchase Order Relation in 0NF57. Purchase Order Relation in 1NF58. Purchase Order Relations in 2NF59. Purchase Order Relations in 3NF60. Normalized Relations61. BCNF – Boyce Codd Normal Form62. Structured Query Language63. What We Can Do with SQL ?64. SQL Commands65. SQL CREATE DATABASE66. SQL CREATE TABLE67. SQL DROP68. SQL Constraints69. SQL NOT NULL70. SQL PRIMARY KEY71. SQL CHECK72. SQL FOREIGN KEY
73. SQL ALTER TABLE74. SQL INSERT INTO75. SQL INSERT INTO SELECT76. SQL SELECT77. SQL SELECT DISTINCT78. SQL WHERE79. SQL AND & OR80. SQL ORDER BY81. SQL UPDATE 82. SQL DELETE83. SQL LIKE84. SQL IN85. SQL BETWEEN86. SQL INNER JOIN87. SQL LEFT JOIN88. SQL RIGHT JOIN89. SQL UNION90. SQL AS 91. SQL Aggregate Functions92. SQL Scalar functions93. SQL GROUP BY94. SQL HAVING95. Database Administration96. SQL Database Administration
![Page 4: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/4.jpg)
Introduction to Databases
Key terms to get know…
• Data ?• Information ?• Database ?• Database System ?
![Page 5: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/5.jpg)
Data
Data are numbers, characters, images or other outputs from devices that is more suitable to
move or process. Data can be known as distinct types of information.
![Page 6: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/6.jpg)
Information
Information is a result of processing and manipulating and organizing data that adds to
the knowledge of the person receiving it.
![Page 7: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/7.jpg)
Database
Database is a collection of interrelated data items that can be processed by one or more
application systems.
![Page 8: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/8.jpg)
Database System
An information system that consists from…
Database DBMS Hardware Software People
![Page 9: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/9.jpg)
Database Applications
• Library• University• Banking• Telecommunication• Sales and Distribution• Manufacturing• Human Resources• Airline
![Page 10: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/10.jpg)
Evolution of Databases
![Page 11: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/11.jpg)
Types of Database Models
![Page 12: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/12.jpg)
Traditional Files Based Systems
![Page 13: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/13.jpg)
Limitations in Traditional Files
• Data Redundancy• Inconsistent Data• Inflexibility• Limited Data Sharing• Poor Data Control• Security Problems• Data Isolation
![Page 14: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/14.jpg)
The Database Approach
![Page 15: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/15.jpg)
Advantages of Database Approach
• Minimal Data Redundancy• Consistency of Data• Flexibility• Sharing of Data• Data Control• Proper Security• Integration of Data• Ease of Application Development• Data Manipulation
![Page 16: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/16.jpg)
Disadvantages of Database Approach
• Complexity• Size• Cost of DBMS• Additional Hardware Cost• Higher Impact of a Failure• Cost of Conversion
![Page 17: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/17.jpg)
Database Management Systems (DBMS)
DBMS is a software that enables users to define, create, maintain and control the access to a
database.
![Page 18: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/18.jpg)
DBMS Functions
A. Data DefinitionB. Data EntryC. Data ManipulationD. Data DisplayE. Data SecurityF. Data IntegrityG. Backup and Recovery
![Page 19: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/19.jpg)
Database Architecture
![Page 20: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/20.jpg)
ANSI-SPARC 3 Level Architecture
![Page 21: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/21.jpg)
ANSI-SPARC 3 Level Architecture
• External Schema– Defines the external view of data
as seen by a particular user or program
• Conceptual Schema– Defines the logical view of the data
as seen by all users and programs
• Internal Schema– Defines the physical view of data
as seen by a DBMS
![Page 22: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/22.jpg)
The Relational Data Model
Data elements are stored in different tables made up of rows and columns. Relates data in
different tables through the use of common data element(s).
![Page 23: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/23.jpg)
What is a Relation?
Data is presented to the user as tables:• Tables are comprised of rows and a fixed number of
named columns.• Columns are attributes describing an entity. Each column
must have an unique name and a data type.
![Page 24: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/24.jpg)
The Relational Data Model
![Page 25: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/25.jpg)
The Relational Data Model
![Page 26: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/26.jpg)
The Relational Data Model
![Page 27: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/27.jpg)
The Relational Data Model
![Page 28: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/28.jpg)
Primary Key
Each table has a primary key. The primary key is a column or combination of columns that uniquely identify each row of the table.
(Composite Key)
![Page 29: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/29.jpg)
Cardinality and Degree
The cardinality of a table refers to the number of rows in the table.
The degree of a table refers to the number of columns.
![Page 30: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/30.jpg)
Relationships
![Page 31: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/31.jpg)
Relationships
A database is a group of related files.
![Page 32: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/32.jpg)
Relationships
![Page 33: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/33.jpg)
Foreign Key
A foreign key is a set of columns in one table that serve as the primary key in another table.
![Page 34: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/34.jpg)
Data Integrity
Data Integrity refers to the validity of data.
Problems may encounter!
• Two employees with same NID, EmpNo?• Employee who is 10 years or 70 years?• Employee who does not work for you?
Solutions?
• Entity Integrity• Domain Integrity• Referential Integrity
![Page 35: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/35.jpg)
Data Dictionary
A Data Dictionary is a file or a set of files that contains a database's metadata.
Names of all tables and their owners.Names of all indexes and the tables in those indexes
relate.Constraints defined on tables.
![Page 36: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/36.jpg)
Database Design
The database design process can be broken down into four phases.
Requirements Collection and Analysis
Conceptual Design
Logical Design
Physical Design
![Page 37: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/37.jpg)
Requirements Collection and analysis
Prospective database uses are interviewed to understand and document their data requirements.
![Page 38: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/38.jpg)
Conceptual Design
This is high level description of the structure of a database.
E.g. E-R diagram
![Page 39: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/39.jpg)
Logical Design
This is the process of mapping the database structure developed in the previous phase to a particular database model.
E.g. map E-R model to relational
![Page 40: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/40.jpg)
Physical Design
This is the process of defining structure that enables the database to be queried in an efficient manner.
![Page 41: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/41.jpg)
Entity Relationship Model
• An Entity Relationship Model is a data model for describing the data within databases or information systems.
• It’s a graphical representation of entities and their relationships to each other.
![Page 42: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/42.jpg)
A mini-world example
• A Company is organized in to departments.• Each department has a number and an
employee who manages the department.• We keep track of the start date when that
employee started managing the department. • A department may have several locations.• A department controls a number of projects.
Each of which has a name, a number and a single location.
![Page 43: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/43.jpg)
A mini-world example cont’d
• We store each employee’s name, national Id number, address, salary, birth date and sex.
• An employee is assigned to one department, but may work on several projects.
• We keep track of the number of hours per week that an employee works on each project.
• We also keep track of the direct supervisor of each employee.
![Page 44: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/44.jpg)
A mini-world example cont’d
• We keep track of the dependants of each employee for insurance purposes.
• We keep each dependant’s name, sex, birth date and relationship to the employee.
Such information is gathered from the mini-world to perform Phase 1 of database design process.
![Page 45: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/45.jpg)
Entities
![Page 46: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/46.jpg)
Relationships
![Page 47: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/47.jpg)
Relationships
![Page 48: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/48.jpg)
Relationships
![Page 49: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/49.jpg)
Relationships
![Page 50: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/50.jpg)
Relationships
![Page 51: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/51.jpg)
Relationships
![Page 52: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/52.jpg)
Relationships
![Page 53: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/53.jpg)
Relationships
![Page 54: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/54.jpg)
Relationships
![Page 55: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/55.jpg)
ERD Notations
![Page 56: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/56.jpg)
Cardinality
![Page 57: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/57.jpg)
Optional Participation
When the number of participants in the relationship is zero
![Page 58: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/58.jpg)
Entities and Relationships
![Page 59: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/59.jpg)
Attributes
• Simple Attribute
• Multi-valued Attribute
• Composite Attribute
![Page 60: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/60.jpg)
Attributes
• Derived Attribute
• Identifier
• Composite Identifier
![Page 61: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/61.jpg)
Entity Relationship Diagram
![Page 62: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/62.jpg)
Entities
• Strong (Regular) Entity
• Weak Entity
• Identifying Relationship
![Page 63: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/63.jpg)
ERD Showing Weak Entities
![Page 64: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/64.jpg)
Relationships Cont’d
• Unary Relationship
• Ternary Relationship
![Page 65: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/65.jpg)
Super Type / Sub Type Relationships
![Page 66: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/66.jpg)
Mapping ERD to Relational
Step 1: Map Regular Entities2: Map Weak Entities3: Map Binary Relationships4: Map Associated Entities5: Map Unary Relationships6: Map Ternary (and n-ary) Relationships7: Map Supertype/Subtype Relationships
![Page 67: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/67.jpg)
1: Map Regular Entities
![Page 68: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/68.jpg)
1: Map Regular Entities
Project (Proj_No, Location, Proj_Name)
![Page 69: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/69.jpg)
2: Map Weak Entities
![Page 70: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/70.jpg)
3: Map Binary Relationships
1. Map Binary One-to-Many Relationships2. Map Binary Many-to-Many Relationships3. Map Binary One-to-One Relationships
![Page 71: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/71.jpg)
3.1 Map Binary One-to-Many Relationships
![Page 72: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/72.jpg)
3.1 Map Binary One-to-Many Relationships
![Page 73: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/73.jpg)
3.2 Map Binary Many-to-Many Relationships
![Page 74: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/74.jpg)
3.3 Map Binary One-to-One Relationships
![Page 75: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/75.jpg)
4: Map Associated Entities
![Page 76: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/76.jpg)
5: Map Unary Relationships
![Page 77: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/77.jpg)
6: Map Ternary (and n-ary) Relationships
![Page 78: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/78.jpg)
7: Map Supertype/Subtype Relationships
![Page 79: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/79.jpg)
Normalization
• In relational database design, the process of organizing data to minimize redundancy.
• Normalization usually involves dividing a database into two or more tables and defining relationships between the tables.
![Page 80: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/80.jpg)
Advantages of Normalization
Reduction of data redundancy within tables:
Reduce data storage space. Reduce inconsistency of data. Remove insert, update and delete anomalies. Improve flexibility of the system.
![Page 81: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/81.jpg)
Disadvantages of Normalization
Reduction in efficiency of certain data retrieval as relations may be joined during retrieval.
• Increase join• Increase use of indexes: storage (keys)• Increase complexity of the system
![Page 82: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/82.jpg)
Normal Forms
1NF any multi-valued attributes have been removed
2NF any partial functional dependencies have been removed
3NF any transitive dependencies have been removed
BCNF any remaining anomalies that result from functional dependencies have been removed
![Page 83: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/83.jpg)
Functional Dependency
Functional Dependency is a constraint between two attributes or two sets of attributes
The functional dependency of B on A is represented by an arrow: A → B
e.g.NID → Name, Address, Birth dateVID → Model, ColorISBN → Title, Author, Publisher
![Page 84: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/84.jpg)
Purchase Order Relation in 0NF
![Page 85: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/85.jpg)
First Normal Form - 1NF
• No multi valued columns exists.• All the key attributes are defined.• All non-key attributes are fully functionally
dependent on the primary key.
![Page 86: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/86.jpg)
Purchase Order Relation in 0NF
![Page 87: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/87.jpg)
Purchase Order Relation in 0NF
![Page 88: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/88.jpg)
1NF - Actions Required
1. Examine for repeat groups of data2. Remove repeat groups from relation3. Create new relation(s) to include repeated
data4. Include key of the 0NF to the new relation(s)5. Determine key of the new relation(s)
![Page 89: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/89.jpg)
Purchase Order Relation in 0NF
![Page 90: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/90.jpg)
Purchase Order Relation in 1NF
![Page 91: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/91.jpg)
Purchase Order Relation in 1NF
![Page 92: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/92.jpg)
Purchase Order Relation in 1NF
![Page 93: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/93.jpg)
Problems - 1NF
INSERT PROBLEMCannot know available parts until an order is placed (e.g. P4 is bush)
DELETE PROBLEMLoose information of part P7 if we cancel purchase order 115 (e.g. Delete PO-PART for Part No P7)
UPDATE PROBLEM:To change description of Part P3 we need to change every record in PO-PART containing Part No P3
![Page 94: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/94.jpg)
Second Normal Form - 2NF
• Relations should not contain any partial functional dependencies.
• E.g. No attribute is dependent on only a partial of the primary key.
![Page 95: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/95.jpg)
PO-PART Relation (Parts Ordered) in 1NF
Part Description is depended only on Part No, which is part of the key of PO-PART.
![Page 96: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/96.jpg)
Parts Ordered Relation in 1NF
![Page 97: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/97.jpg)
2NF - Actions Required
If entity has a concatenated key
1. Check each attribute against the whole key2. Remove attribute and partial key to new
relation3. Optimize relations - consider combining tables
that have identical primary keys
![Page 98: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/98.jpg)
Parts Ordered Relation in 1NF
![Page 99: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/99.jpg)
Parts Ordered Relations in 2NF
![Page 100: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/100.jpg)
Purchase Order Relations in 2NF
![Page 101: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/101.jpg)
Purchase Order Relation in 2NF
![Page 102: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/102.jpg)
Problems - 2NF
INSERT PROBLEMCannot know available suppliers until an order is placed (e.g. 200 is hardware stores)
DELETE PROBLEMLoose information of supplier 100 if we cancel purchase order 116 (e.g. Delete PO for Supplier No 100)
UPDATE PROBLEMTo change name of Supplier 222 we need to change every record in PO containing Supplier No 222
![Page 103: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/103.jpg)
Third Normal Form - 3NF
• No any transitive dependencies are exist.• Transitive dependency is a functional
dependency between two or more non-key attribute.
![Page 104: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/104.jpg)
PO Relation in 2NF
Supplier name is a non-key field depended on another non-key field (supplier no) in addition to be depended on the key purchase order no.
![Page 105: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/105.jpg)
Purchase Order Relation in 2NF
![Page 106: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/106.jpg)
3NF - Actions Required
1. Check each non-key attribute for dependency against other non-key fields
2. Remove attribute depended on another non-key attribute from relation
3. Create new relation comprising the attribute and non-key attribute which it depends on
4. Determine key of new relation
![Page 107: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/107.jpg)
Purchase Order Relation in 2NF
![Page 108: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/108.jpg)
PO and SUPPLIER Relations in 3NF
![Page 109: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/109.jpg)
Purchase Order Relations in 3NF
![Page 110: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/110.jpg)
Purchase Order Relation in 3NF
![Page 111: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/111.jpg)
Normalized Relations
![Page 112: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/112.jpg)
BCNF – Boyce Codd Normal Form
• Boyce Codd Normal Form is a higher version of the Third Normal form.
• In BCNF Every determinant in table is a candidate key.
A table that is in 3NF but not in BCNF
![Page 113: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/113.jpg)
3NF without BCNFSTU_ID STAFF_ID CLASS_CODE ENROLL_GRADE125 25 21344 A125 20 32456 C135 20 28458 B135 25 27563 C144 20 32456 B
• Each Class_Code identifies a class uniquely.
• A student can take many classes.
• A staff member can teach many classes, but each class is tought by only one staff.
![Page 114: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/114.jpg)
3NF without BCNF
PROBLEMS
• If a different member is assigned to teach class 32456 two rows must be updated.
• Also if student 135 drops out we lose data on who teaches the class.
STU_ID STAFF_ID CLASS_CODE ENROLL_GRADE125 25 21344 A125 20 32456 C135 20 28458 B135 25 27563 C144 20 32456 B
![Page 115: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/115.jpg)
BCNF – Boyce Codd Normal Form
STU_ID STAFF_ID ENROLL_IDCLASS_CODE
CLASS_CODESTU_ID ENROLL_ID CLASS_CODE ENROLL_ID
3NF but not in BCNF
3NF and BCNF
![Page 116: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/116.jpg)
BCNF – Boyce Codd Normal Form
STU_ID CLASS_CODE ENROLL_GRADE125 21344 A125 32456 C135 28458 B135 27563 C144 32456 B
CLASS_CODE STAFF_ID21344 2532456 2028458 2027563 25
![Page 117: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/117.jpg)
Structured Query Language (SQL)
• SQL is using for storing, manipulating and retrieving data stored in relational database.
• All relational DBMS like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server uses SQL as standard database language.
![Page 118: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/118.jpg)
What We Can Do with SQL ?
Access data in relational DBMS. Define the data in database Manipulate the data in database. Create and drop databases and tables. Create view, stored procedure in a database. Set permissions on tables, procedures, and
views.
![Page 119: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/119.jpg)
SQL Commands
![Page 120: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/120.jpg)
SQL Commands
![Page 121: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/121.jpg)
SQL CREATE DATABASE
CREATE DATABASE DBstudent;
![Page 122: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/122.jpg)
SQL CREATE TABLE
CREATE TABLE tblStudent(StudentID int,FirstName varchar(50),LastName varchar(50),Address varchar(255),Phone varchar(50));
![Page 123: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/123.jpg)
SQL DROP
DROP TABLE table_name;
DROP DATABASE database_name;
![Page 124: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/124.jpg)
SQL Constraints
SQL constraints are used to specify rules for the data in a table.
NOT NULLPRIMARY KEYCHECKFOREIGN KEY
![Page 125: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/125.jpg)
SQL NOT NULL
CREATE TABLE tblPayment(PaymentID int NOT NULL,Amount varchar(255) NOT NULL,PayedDate datetime) ;
![Page 126: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/126.jpg)
SQL PRIMARY KEY
CREATE TABLE Course(CourseID int NOT NULL,CourseName varchar(255) NOT NULL,Duration varchar(50),CourseFee varchar(50),PRIMARY KEY (CourseID));
![Page 127: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/127.jpg)
SQL CHECK
CREATE TABLE tblStudent(StudentID int NOT NULL,FirstName varchar(50),LastName varchar(50),Address varchar(255),Phone varchar(50),CHECK (StudentID > 1000));
![Page 128: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/128.jpg)
SQL FOREIGN KEY
CREATE TABLE tblPayment(PaymentID int NOT NULL,Amount varchar(255) NOT NULL,PayedDate datetime, StudentID int,PRIMARY KEY (PaymentID),FOREIGN KEY (StudentID) REFERENCES tblStudent(StudentID));
![Page 129: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/129.jpg)
SQL ALTER TABLE
ALTER TABLE tblStudent ADD DateOfBirth date;
ALTER TABLE tblStudent MODIFY COLUMN DateOfBirth year;
ALTER TABLE tblStudent DROP COLUMN DateOfBirth;
![Page 130: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/130.jpg)
SQL INSERT INTO
INSERT INTO tblStudent (StudentID, FirstName, LastName, Address, Phone) VALUES (1000, 'Thilina', 'Perera', 'Colombo, Sri Lanka', '0777475323');
![Page 131: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/131.jpg)
SQL INSERT INTO SELECT
INSERT INTO tblStudent (StudentID, FirstName, LastName, Address, Phone) SELECT EmpID, FirstName, LastName, Address, Phone FROM tblEmployer;
![Page 132: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/132.jpg)
SQL SELECT
SELECT * FROM tblStudent;
SELECT StudentId, FirstName FROM tblStudent;
![Page 133: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/133.jpg)
SQL SELECT DISTINCT
SELECT DISTINCT FirstName FROM tblStudent;
![Page 134: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/134.jpg)
SQL WHERE
SELECT * FROM tblStudent WHERE StudentID=1;
![Page 135: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/135.jpg)
SQL AND & OR
SELECT * FROM tblStudent WHERE Address='Matara' OR Address='Colombo';
SELECT * FROM tblStudent WHERE FirstName='Roshan' AND Address='Colombo';
![Page 136: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/136.jpg)
SQL ORDER BY
SELECT * FROM tblStudent ORDER BY FirstName DESC;
SELECT * FROM tblStudent ORDER BY FirstName ASC;
![Page 137: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/137.jpg)
SQL UPDATE
UPDATE tblStudent SET FirstName='Sampath', Address='Kandy' WHERE StudentID=2;
![Page 138: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/138.jpg)
SQL DELETE
DELETE FROM tblStudent WHERE StudentID=3;
![Page 139: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/139.jpg)
SQL LIKE
SELECT * FROM tblStudent WHERE FirstName LIKE 'S%';
SELECT * FROM tblStudent WHERE FirstName LIKE '_uwan';
![Page 140: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/140.jpg)
SQL IN
SELECT * FROM tblStudent WHERE Address IN ('matara','galle');
SELECT * FROM tblStudent WHERE Address NOT IN ('matara','galle');
![Page 141: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/141.jpg)
SQL BETWEEN
SELECT * FROM tblStudentWHERE StudentID BETWEEN 1000 AND 2000;
![Page 142: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/142.jpg)
SQL INNER JOIN
SELECT tblStudent.FirstName, tblPayment.PaymentID FROM tblStudent INNER JOIN tblPayment ON tblStudent.StudentID=tblPayment.StudentID;
![Page 143: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/143.jpg)
SQL LEFT JOIN
SELECT tblStudent.FirstName, tblPayment.PaymentID FROM tblStudent LEFT JOIN tblPayment ON tblStudent.StudentID=tblPayment.StudentID;
![Page 144: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/144.jpg)
SQL RIGHT JOIN
SELECT tblPayment.PaymentID, tblStudent.FirstName FROM tblStudent RIGHT JOIN tblPayment ON tblStudent.StudentID=tblPayment.StudentID;
![Page 145: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/145.jpg)
SQL UNION
SELECT FirstName FROM tblStudent UNION SELECT FirstName FROM tblStudent2;
SELECT FirstName FROM tblStudent UNION ALL SELECT FirstName FROM tblStudent2;
![Page 146: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/146.jpg)
SQL AS (Aliases)
SELECT StudentID AS 'Student ID' FROM tblStudent;
SELECT S.FirstName, P.PaymentID FROM tblStudent AS S INNER JOIN tblPayment AS P ON S.StudentID=P.StudentID;
![Page 147: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/147.jpg)
SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.
AVG() - Returns the average valueCOUNT() - Returns the number of rowsFIRST() - Returns the first valueLAST() - Returns the last valueMAX() - Returns the largest valueMIN() - Returns the smallest valueSUM() - Returns the sum
![Page 148: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/148.jpg)
SQL Scalar functions
SQL scalar functions return a single value, based on the input value.
UCASE() - Converts a field to upper caseLCASE() - Converts a field to lower caseINITCAP() - Converts the first letter of a field to
upper case.
![Page 149: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/149.jpg)
SQL GROUP BY
SELECT StudentID, SUM(Amount) FROM tblPayment GROUP BY StudentID;
![Page 150: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/150.jpg)
SQL HAVING
SELECT StudentID, SUM(Amount) FROM tblPayment GROUP BY StudentID HAVING SUM(Amount)>2000;
![Page 151: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/151.jpg)
Database Administration
Installing and UpgradingDatabase SecurityEnrolling UsersMonitoring ActivitiesOptimizing the PerformanceProducing ReportsBackup and Recovery
![Page 152: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/152.jpg)
SQL Database Administration
GRANT SELECT, INSERT ON dbstudent.* TO 'silva'@'localhost' IDENTIFIED BY 'silva123';
REVOKE INSERT ON dbstudent.* FROM 'silva'@'localhost';
![Page 153: DISE - Database Concepts](https://reader038.vdocuments.us/reader038/viewer/2022102811/58a885231a28abb47a8b5f1f/html5/thumbnails/153.jpg)
The End
http://twitter.com/rasansmn