Week 2 Flashcards
What is the Three-level Architecture?
View 1 View 2 View 3 1*
\ | /
\ | /
Conceptual Level 2*
|
|
Internal Schema 3*
|
|
Database
1* The way users percieve the data
2* Provide both mapping and desired independance
how to match user view to actual data stored in internal level
3* The way DMBS and the operating system perceive the data
What is the External level of Three-Level Architecture?
View Level
Describes part of the data that is relevant to users
What is the Conceptual Level in Three-Level Architecture?
Describes WHAT data are stored in the database and relationships among the data
What is the Internal Level in Three-Point Architecture?
Describes HOW data is actually stored and how files and indexes are organised and used
What is Data Abstraction?
Hide storage details and present users with conceptual view of database
What is Data Independance?
refers to the system’s ability to change the database schema at one level of a database system without having to change the schema at the next higher level
What is Database Schema?
Description of the structure of Database
What is Database State?
Content of DB at moment in time
What is Initial Database State?
Content of the Database when it is created
What is a Data Model?
Set of concepts to describe:
- Data and relationships among data
- Data constraints
What is a Conceptual Data Model?
Identifies high level data structure
Independant of DBMS, application programs and physical considerations
What are the 3 types of Logical Data Models (hint: H,N,R)?
- Hierarchical
- Network
- Relational
What is a Hierarchical Data Model?
Data is organised in tree-like structure
a node only has one parent
What is a Network Data Model?
Data organised into graph
One node can have more than 1 parent at a time
What is a Relational Data Model?
Way of structuring data in database that emphasizes relationships between data entities
What is a Physical Data Model?
Describe how data is stored in the computer, representing record structures, record orderings and access paths
Highly dependant on target DBMS
What are the main differences between a File-Based Approach and the Database Approach?
In File-Based Approach, each application program defines and manages its own data
While in Database approach, a shared database is stored to control the redundancy and ensure the data consistency and integrity
In the File-Based Approach, each application program directly accesses the data
While in the database approach, all applications access the database through DBMS which provides a few important access control facilities
What is the Conceptual Level in the Three-Level Architecture?
Describes WHAT data is stored in the database and the relationships among the data
Emphasis on logical structure of the data. Must not contain any storage-dependant details
What is the Internal Level in the Three-Level Architecture?
Describes HOW data are actually stored and how files and indexes are organized and used
What are 3 Categories of Data Model?
- Conceptual Data Model
- Logical Data Model
- Physical Data Model
What is a Conceptual Data Model?
Identifies the high level data structure
Independant of DBMS, application programs and physical considerations
example: Entity-Relationship (ER) Model proposed in 1976
What does the Entity-Relationship (ER) model solve?
One of the most difficult aspects of database design is that designers, programmers and end-users tend to view data in different ways
Need a model for communication that is non-technical and free of ambiguities
Solution:Entity-Relationship (ER) Model
What are the 4 Components in an ER Diagram?
- Entity
- Relationship
- Attribute
- Constraint
What is an Entity (ER Diagram)?
A group of objects with the same properties
- Objects with a physical existence:
eg. student, classroom, book, property
- Objects with a conceptual existence
eg. Viewing, Inspection, Sale
What is a Relationship (ER Diagram)?
Relationship (type): meaningful associations among two or more entities
Degree of Relationship: The number of participating entity types in a relationship
What is an example of a binary (degree 2) relationship type?
. Enrolls on>
Student—————————————–Course
<Views PropertyForRent-----------------------------Client
What is an example of a ternary (degree 3) relationship type?
An admissions tutor enrolls a student onto a course
Admissions Tutor —-EnrollsOn—-Course
|
|
Student
What is an example of a quarternary (degree 4) relationship type?
An international student, under the active guidance of his high school tutor, applies for a place at a UK University through a recruiting agent
What is a recursive (degree 1) relationship type?
A relationship between occurrences of the same entity type
eg. AdademicStaff (professor) supervises AcademicStaff (lecturer)
What is a Multiple Relationship?
2 Entities are associated through more than one relationship
What is an Attribute (ER Diagram)?
A property of an entity or a relationship
Student
———–
studentNo {PK} <– (Primary key)
StudentName ]———— ** Attributes**
dOBirth
…
What is a Composite Attribute?
An attribute that contains multiple smaller attributes
eg.
studentName
fName
lName
homeAddress
street
city
postcode
What is a Derived Attribute?
An attribute whose value is calculated or derived from other attributes in the database rather than being stored directly
If you have an attribute “Date of Birth” for an entity “Person,” you could derive the “Age” attribute from the current date and the “Date of Birth”
The “Age” is not stored, but calculated whenever needed
What is a Multi-valued Attribute?
A multi-valued attribute in an ER (Entity-Relationship) diagram is an attribute that can have multiple values for a single entity
For example:
A “Person” can have multiple phone numbers or email addresses. Here, “Phone Numbers” is a multi-valued attribute because a person can have more than one phone number
What is a Candidate Key?
A Candidate Key is an attribute or a set of attributes that uniquely identifies each row in a database table.
-A candidate key can not contain a null
eg. National insurance number, staff number
What is the Primary Key?
The candidate key that is selected to uniquely identify each occurrence of an entity type
Principles of choosing the Primary Key:
-Attribute length
-Minimal num of attributes required
- The future certainty of uniqueness
What is the Alternate Key?
An alternate key is any other attribute that could also be used to uniquely identify a record, but isn’t selected as the primary key
