Topic 1.4 Relational Databases Week 4 Flashcards
What is a database
- efficiently and centrally coordinates information for a related group of files (database)
File - a group of related records
Record - a group of related fields
A field is a specific attribute of interest for the entity
What is the difference between database and file based legacy
File based legacy - had multiple master file databases that had facts
Database system - keeps facts that used to be in master files in a database so they can be accessed whenever. Not stored somewhere else
Advantages of databases
- data is integrated
- data sharing
- minimises data redundancy and inconsistencies
- data is independent of the programs that use the data
- data is easily accessed for reporting and cross functional analysis
Logical view of data has
Are at an external level of the database. Can access it but don’t worry how it’s stored etc
Physical view data base
Internal view of database. Interested how data is stored in the system
Know what people want access to and what they want stored etc
What does the database management system do
Translates users logical view into instructions as to which data is to be retrieved from the data base
What does operating system do
Translate database management systems into machine code to physically retrieved data
What are schemas
A plan or layout. 3 types
Conceptual level - organisation wide view
External level - individuals users view
Internal level - low level view
What are the 3 different database management system languages
Data definition language (DDL)
- builds the data dictionary
- creates the database
- describes the logical views for each user
- specifies record or field security constraints
Data manipulation language (DML)
- changes the content in the database
Creates, updates, insertions, and deletions
Data Query Language (DQL)
- enables users to retrieve, sort, and display specific data from the database
What does relationally database do
- Represents the conceptual and external schema as if that “data view” were truly stored in one table
What are primary keys
The key that identifies the record in a table e.s in a sales order it’s the sales number
An attribute or combination of attributes that can be used to uniquely identify a specific row (record) in a table
What is a foreign key
Is a key that links two tables together. The foreign key in one table is the primary key in another table
An attribute in one table that is a primary key on another table
- used to link two tables
What are non key attributes
Non-key attributes which store important data about the entity
Why have a set of related tables?
Data stored in one large table can be redundant and inefficient causing the following problems:
- update anomaly
- insert anomaly
- delete anomaly
Rules for relational databases
- every column in a row must have a single value
- primary key cannot be null (empty) also known as entity integrity
- if a foreign key is not null, it must have a value that corresponds to the value of a primary key in another table (referential integrity)
- all other attributes in the table must describe characteristics of the object identified by the primary key
Following these rules allows databases to be normalised and solves the update, insert and delete anomalies
What does normalisation do in relational databases
Initially, one table is used for all the data in a database
The table is decomposed into multiple smaller tables related by primary and foreign keys
This is called 3rd normal form (3NF)