A.2 The Relational Database Model Flashcards
Database Management System
System software for creating and managing databases.
Provides users and programmers with a systematic way to create, retrieve, update and manage data
Relational database management system (RDBMS)
–> A database management system based on the relational model
–> Stored in database objects which are called tables
–> Each table are broken up into smaller entities called fields.
Record
A row of data is rach individual entry that exists in a table
Column
A vertical entity in a table that contains all information associated with a specific field in a table
Functions and tools of a DBMS
- Organises Data –> organized or structured according to the specifications
- Integrates Data –> Data is linked together so that it can be assembled into combinations during the execution of particular applications.
- Separates Data –> serves as a filter between application programs and their associated data.
- Controls Data –> controls where data is physically stored.
- Retrieves Data –> record of data can be obtained via DBMS
- Protects Data –> protects the data and relational elements from unauthorized users, physical damage, operating system failure, etc.
Security of DBMS
Data validation, access rights and data locking are all features that promote data security.
Schema
The logical structure of data in the database
Conceptual Schema
A basic model that contains entities and the relationship between them
Logical Schema
Each entity in the conceptual schema is converted into either a table, field, or object with any restrictions needed.
Physical Schema
It contains the name of the tables, a list of fields in each table, and relationships between tables that are specific to database management software.
Nature of Database Dictionary
Contains data about each file in the database and each field within those files
Data Definition Language (DDL)
Programming language used to define and create database schemas.
Table
The place where records and fields are stored
Record
Groups of related fields
Field
Combination of one or more one character
Primary Key/ Candidate Key
Unique key field which identifies a record in the table
Secondary key
A unique key field which identifies a record in another table
Composite Secondary key
Combination of more than 1 primary key
Join
Collection of fields
One to One
When each record in one table is linked to only one record in the other table.
One to Many
One row in table A may be linked with many rows in table B, but one row in table B is linked to only one row in table A
Many to Many
When one or more items in one table can have a relationship to one or more items in another table
Issues Caused by Redundant Data
Insertion Anomalies –> new rows –> duplicate data
Deletion Anomalies –> Deleting rows may cause a loss of data
Update Anomalies –> inconsistency in the data that happens after database is updated
Referential Integrity
Refers to the relationship between tables.
–>It maintains the primary keys that are used to create a normalized database.
1NF
One table
2NF
Multiple tables that rely on primary keys
3NF
Eliminates non-primary keys from relying on other non-primary keys
Different data types (9)
Text
Numeric
AutoNumber
Currency
Date
Memo
Yes/No
Hyperlink
Object (photos, videos, audios)
Construct a relational database to 3NF using objects such as tables, queries, forms, reports, and macros
-
Construct an Entity-Relationship diagram (ERD) for a given situation
-
Simple Query
Simple queries are made using one table.
Complex Query
Complex queries are made using multiple tables and complex conditions
SQL Statement
SELECT LastName, Phone,Email
FROM STUDENT
WHERE EMAIL LIKE “%@gmail.com”
