![Page 1: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/1.jpg)
Database Management
2. course
![Page 2: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/2.jpg)
Reminder
• Concept of DBMSs:– Gathering all the data and correspondence into one DB– Answer the question with the help of this DB
• 3 levels of a DB– Conceptual model: A world described by the DB– Implementation/representation model: a model
understandable for the DBMS (structured records, tables, fields, etc.)
– Physical model: DBMS implemented on the computer (files, programs)
![Page 3: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/3.jpg)
Structure of the DBMS
• The user has permission for the smaller part of the DB: View
Conceptual model
Implementation model
Physical model
View1 View2 View3
![Page 4: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/4.jpg)
Relational data model
• Relation:table+constraints
• Column headers:attribute/domain
• Rows:data records/tuples
• Database:Set of tables
![Page 5: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/5.jpg)
Relationships
• 1:1
• 1:N
• N:M
Person Owns Car
Actor Acts Play
![Page 6: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/6.jpg)
• Superkey/Key/Foreign key• Anomalies
– Insertion: superkey needs too much data– Update: Redundancy– Deletion: Different objects stored together
![Page 7: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/7.jpg)
Normalization
• If a relation is in 0 NF (can be put in tables) and does not contain multiple field then it is in at least 1 NF
• If a relation is in 1 NF and does not contain partial functional dependency then it is in at least 2 NF
• If a relation is in 2 NF and does not contain transitive functional dependency then it is in at least 3 NF
![Page 8: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/8.jpg)
3 NF vs. BCNF
• A 3 NF is not in BCNF if– There are several possible keys,– these keys are composite, and– there is a common attribute in the keys
Decomposition has to be…???
![Page 9: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/9.jpg)
Decomposition
• Has to be– Lossless: By uniting/jointing the decomposed tables,
the original tables before normalization can be created
– Preserve dependencies: After decomposition, the originial dependencies can be infered from the new relation’s dependencies.
• Closure of an attribute set• Armstrong axioms• How to check?
![Page 10: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/10.jpg)
Today
• Administration• Concurrent processing• Entity-relationship diagrams
![Page 11: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/11.jpg)
Tasks of the database supervisor
• Administration• Protect the systems• Protect the users• Design• Keep in touch with the users• Maintain, save, backup
![Page 12: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/12.jpg)
Logical independence
Conceptual model
Implementation model
Physical model
View1 View2 View3
![Page 13: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/13.jpg)
Physical independence
Conceptual model
Implementation model
Physical model
View1 View2 View3
![Page 14: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/14.jpg)
Transaction
• Unit• Basic operation in the DB• Has to preserve the consistency of the DB
![Page 15: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/15.jpg)
Concurrent processing
• Basic requirement for fast execution• Inconsistency• User is independent
![Page 16: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/16.jpg)
Steps of record processing
1. Search2. Import to the workspace of the user3. Process, update4. Write back to database
![Page 17: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/17.jpg)
Wait-for graph
• Ek(Ti, Tj): Ti gets mean k before Tj
• Ti: ith transaction
![Page 18: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/18.jpg)
Rollback
• Rollback journal• Difference table
![Page 19: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/19.jpg)
Log
• Atomicy• WAL protocol• Crash?
![Page 20: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/20.jpg)
Logfile
• Content• Concatenated or rolled back• Archived• Invisible
![Page 21: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/21.jpg)
Tasks of the DB administrator
• Data independence• Comfort• Security, secrecy, accuracy• Concurrent processes• Reduce response time• Develop• Expenses
![Page 22: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/22.jpg)
Structure of the DBMS
• LayersQuery optimization and
-execution
Relational operators
Files and permissions
Buffering
Handling storage
DB
Handling concurrency andrecovery control is takeninto consideration
![Page 23: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/23.jpg)
Entity relationship (ER) diagrams
![Page 24: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/24.jpg)
Steps of designing a database
• Survey the needs• Design the conceptual model• Create the implementation model• Design the physical model
![Page 25: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/25.jpg)
Steps of the conceptual designing
1. Define the entities and relationships2. Define the info about the entites3. Define the constraints
![Page 26: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/26.jpg)
Elements of the ER model
• Entities– Properties– Key
• Relationships– 1:1– 1:N– N:M
![Page 27: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/27.jpg)
Entity set
• Set of similar entities• Key• Codomain
![Page 28: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/28.jpg)
Notation
• Entity set:
• Attribute:
• Key attribute:
Student
sidname
avg
![Page 29: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/29.jpg)
Relationships and relationship sets
• Relationship:– David works in the purchasing department
• Relationship set:– n-ary relation:, elements of the relation: ,
![Page 30: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/30.jpg)
Notation
• Relationship set:
• M:N relationship:
• N:1 relationship:
lot
dname
budgetdid
sincename
Works_In DepartmentsEmployees
ssn
Reports_To
lot
name
Employees
super-visor
ssn
![Page 31: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/31.jpg)
Types of relationships
lot
dname
budgetdid
since
name
Works_In DepartmentsEmployees
ssn
dname
budgetdid
since
lot
name
ssn
Employees DepartmentsManages
![Page 32: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/32.jpg)
ER model of a university
• What assumptions are there?
• How to check correctness? (Ask questions!)
Students
Professor teaches
Department
faculty
major offers
Courses
enrollmentadvisor
![Page 33: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/33.jpg)
Practice
• Can a student have more than one major?
• Can a student have more than one advisor?
• Can a professor work in more than one department?
• Can a course have more than one teacher?
• Can a department be without teacher?
Students
Professor teaches
Department
faculty
major offers
Courses
enrollmentadvisor
![Page 34: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/34.jpg)
Constraints
• Does all the departments have a manager?– Participation constraint– Bold or double line
lot
name dnamebudgetdid
sincename dname
budgetdid
since
Manages
since
DepartmentsEmployees
ssn
Works_In
![Page 35: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/35.jpg)
Weak entity set
• Attributes of the entity set are not enough– Notation: double or bold line for the entity set
Studios(name, addr)Crews (number, studioName)Unit-of (number, studioName, name)name=studioName!!!
![Page 36: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/36.jpg)
Example
• Disney crew #3 is one of the crews of the Disney studio
• Crews: (Disney-crew-#3, Disney)• Unit-of: (3, Disney, Disney)
• Solution:• Merge to: (3, Disney)• Same as Crews leave it for good
![Page 37: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/37.jpg)
Subclass, inheritance (ISA)
Length Title Year
WeaponMovies
CriminalCartoon
isa isa
isa isa
Criminal cartoon
Voice
Actor
![Page 38: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/38.jpg)
• Special properties can be given• Different relationships with other entities• Inheritance• Overlap constraint• Covering constraint
![Page 39: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/39.jpg)
Decisions in design
• Entity or property?• Entity or relationship?• Relationship: binary or ternary?• Constraints
![Page 40: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/40.jpg)
Entity or attribute?
• Should the address of an employee be an entity or an attribute of the worker table?
• Attribute:– Simple– Atomic
![Page 41: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/41.jpg)
Example
name
Employees
ssn lot
Works_In
from to
dname
budgetdid
Departments
dname
budgetdidname
Departments
ssn lot
Employees Works_In
Durationfrom to
![Page 42: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/42.jpg)
Entity or relationship
Manages2
name dnamebudgetdid
Employees Departments
ssn lot
dbudgetsince
dname
budgetdid
DepartmentsManages2
Employees
name
ssn lot
since
Managers dbudget
ISA
![Page 43: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/43.jpg)
Binary or ternary relationship?
• Every policy belongs to an employee• Every dependent is connected to a policy• Not appropriate modeling:
agepname
DependentsCovers
name
Employees
ssn lot
Policies
policyid cost
![Page 44: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/44.jpg)
Rather binary
• Every policy belongs to an employee• Every dependent is connected to a policy• Appropriate modeling:
Beneficiary
agepname
policyid cost
Policies
Purchaser
name
Employees
ssn lot
Dependents
![Page 45: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/45.jpg)
Rather ternary
• Order contracts connecting Procurers (P), Suppliers (S), and Products (Pr)
• With binary relations: – P orders Pr– Supplier supplies for P– S delivers Pr
pricename
productwriter
name
Procurer
pid addr
supplier
name addr
count
![Page 46: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/46.jpg)
Conclusions
• First step: gathering the needs• ER-model: similar to human thinking• Basic terms: entity, relationship, attribute• Other terms: weak entity, ISA relationship,
constraints
![Page 47: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/47.jpg)
• Constraints are important• ER-model is subjective• Good ER-model gives good DB schema
• Literature:• Jeffrey D. Ullman, Jennifer Widom: A first
course in database systems• www.uni-obuda.hu/users/varkonyi.teri
![Page 48: Database Management 2. course. Reminder Concept of DBMSs: – Gathering all the data and correspondence into one DB – Answer the question with the help](https://reader030.vdocuments.us/reader030/viewer/2022032804/56649e585503460f94b5165f/html5/thumbnails/48.jpg)
Thank you for your attention!