Full summary of principles of database management prof. Bart Baesens. Perfect for integral learning. Includes all chapters of the lesson except part of SQL (H7). If necessary, reference will be made to slides or courses.
Very convenient to use for the task and to learn before the exam.
The summa...
Exam (elaborations) INF3703 Assignment 2 (COMPLETE ANSWERS) 2024 - DUE 28 June 2024 • Course • Databases II - INF3703 (INF3703) • Institution • University Of South Africa • Book • Principl...
Exam (elaborations) INF3703 Assignment 2 (COMPLETE ANSWERS) 2024 - DUE 28 June 2024 • Course • Databases II - INF3703 (INF3703) • Institution • University Of South Africa • Book • Principl...
,1 Fundamental Concepts of Database Management
File vs DB Approach to Data Management
.
File approach: Database Approach:
∗ Duplicate or redundant information ∗ Superior to the file approach in terms of
stored efficiency, consistency and maintenance
∗ Danger of inconsistent data ∗ Loose coupling btw applications and data
∗ Strong coupling btw applications and ∗ Facilities provided for data querying and
data retrieval
∗ Hard to manage concurrency control
∗ Hard to integrate applications aimed at
providing cross-company services
Database model/ scheme provides description of database data at different levels of details and de-
fines the various data items, characteristics and relationships etc. (not expected to change to
frequently)
Database model is comprised of different data models e.g.
∗ Student (number, name, address, email)
∗ Course (number, name)
∗ Building (number, address)
Data model provides a clear and unambiguous description of the data items with their characteristics
and relationships
1. Conceptual DM provides a high-level description of the data items (including description of
relationships)
2. Logical DM is a mapping of the conceptuel DM towards a specific implementation environment
and sets the relationships between them
3. Physical DM describes the database-specific implementation of the data model (refining of the
early design)
→ We mostly look at Logical and Conceptual DM
Database state represents data in db at a particular moment (changes on an ongoing basis)
Principles of Database Management
4/70
, Figure 1: Database State
1.1 The Three Layer Architecture
Figure 2: 3-Layer-Architecture
The Layers can also be defined as followed:
1. Presentation Layer (external) e.g. tables etc.
Views are the visual components responsible for displaying information to a user
2. Application Layer (conceptual/logical)
3. Data Layer (internal) stores and manages all the data
Catalog is the heart of the DBMS and contains the data definition or metadata of de DB application.
– Stores the definitions of the views, logical and internal DM and synchronizes these 3 DM to
guarantee their consistency.
1.2 DB Users
In this order:
1. Information architect designs conceptual DM
2. DB designer translates conceptual DM → logical and internal DM
3. DB administrator (DBA) is responsible for implementation and monitoring of the DB
∗ Uses Data Definition Language (DDL) to express the DB’s external, logical and internal DM
– definitions are stored in the catalog
Principles of Database Management
5/70
, 4. Application Developer develops DB applications in a programming language such as Java or
Python
5. Business user runs these apps to perform specific DB operations
DML (Data Manipulation Language) is used to retrieve, insert, delete and modify data (can be embedded
in progr. lang. or entered through a front-end querying tool)
→ SQL offers both DDL & DML statements for relational DBS
1.3 Advantages of DBS & DBM
1. Data Independence
→ implies that changes in data definitions have minimal to no impact on the apps
Physical data independence implies that neither the apps, nor the views or logical DM must be
changed when changes are made to the data storage specifications in the internal model
− DBMS should provide interfaces between logical and internal DM
Logical data independence implies that software apps are minimally affected by changes in the
conceptual or logical DM
− Views in external DM act as a protective shield
− DBMS must provide interfaces between conceptual logical and external layer
2. Managing structured, semi-structured and unstructured data
∗ Structured data
– Described according to a formal logical DM
– Ability to express integrity rules and enforce correctness of data
– Also facilitates searching, processing and analyzing data
– E.g., number, name, address and email of student
∗ Unstructured data
– No finer grained components in a file or series of characters that can be interpreted in a
meaningful way by a DMS or app
– E.g, document with biographies of famous NY citizens
→ Volume of unstructured data > structured data
∗ Semi-structured data
– Data which does hav a certain structure but the structure may be very irregular or highly
volatile
– E.g., individual users’ webpages on a social media platform or resume documents in a HR
DB.
3. Managing Data Redundancy
∗ Duplication of data can be desired in distributed env. to improve data retrieval
∗ DBMS is now responsible for management of the redundancy by providing synchronization
facilities to safeguard data consistency
∗ Compared to the file approach the DBMS quarantees correctness of the data without user
intervention
4. Specifying Integrity Rules
∗ Syntactical rules specify how the data should be represented and stored
→ CustomerID is integer; birthdate should stored as month, day, year
Principles of Database Management
6/70
, Figure 3: Lost update problem; the balance+120 is lost as a result of inconsistency in the execution of
the operations
∗ Semantic rules focus on the semantic correctness or meaning of the data
→ CustomerID is unique, account balance should be > 0; customer cannot be deleted if he
has pending invoices
∗ Integrity rules are specified as part of the conceptual/ logical DM and stored in the catalog
→ Directly enforced by the DBMS instead of apps
5. Concurrency Control
Concurrency = gelijktijdigheid → parallel execution of de concurrent units
∗ DBMS has built in facilities to support concurrent or parallel execution of DB programs
∗ Key concept is a DB transaction
∗ Read/write operations can be executed at the same time by the DBMS
∗ DBMS should avoid inconsistencies
−→ DBMS must support ACID
A tomicity requires that a transaction should either be executed in its entirety or not at all
C onsistency assures that a transaction brings the database from one consistent state to another
I solation ensures that the effect of concurrent transaction should be the same as if they would
have been executed in isolation (no interaction)
D urability ensures that the DB changes made by a transaction declarred successful can be made
permanent under all circumstances
6. Backup and Recovery Facilities
∗ Backup and recovery facilities can be used to deal with the effect of loss of data due to hardware
or network errors etc.
∗ Backup facilities can either perform a full or incremental backup
∗ Recovery facilities allow to restore the data to a previous state after loss or damage occured
7. Data Security
∗ Some users have read access, whilst others have write access to the data (role-based function-
ality)
∗ Data access can be managed via logins and passwords assigned to users
∗ Each account has its own authorization rules that can be stored in the catalog
8. Performance Utilities 3 KPI’s (key performance indicators) are
Principles of Database Management
7/70
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller SmwBoy123. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $11.15. You're not tied to anything after your purchase.
