Unit 1.3.2 Databases

37 cards·by gurundus

What are databases used for?

Allows for data to be retrieved, updated and filtered, They allow certain users to have restricted data access and stop inconsistencies

What are the types of files?

Serial Files, Sequential Files and Indexing

Why where serial files used?

This was the only way to store data on a long thin medium such as tape

What is a disadvantage of using Serial Files?

Each record has to have the same structure and to locate a record, the whole file had to be searched from start to finnish.

What is an advantage of using Sequential Files?

Searching for specific files is much easier

What is a problem caused by Sequential Files?

there are delays caused by library transactions, it also requires sorting through the data which can be time consuming

What is a disadvantage of using Indexing?

They still have to sort there data.

What is the advantage of using Indexing?

It is quicker to search than sequential files

What is a simple database in indexing called?

Simple databases in these formats are flat-file databases

Why are fixed lenth fields used?

This allows the software to count bytes in order to count fields

What is a disadvantage of using Fixed Length?

storage space can be wasted as not all values will use the allocated space for the field

What are the pros of a Flat File Databases?

Quick and easy to create, Fine for small amounts of data and Great for single-entity models.

What are the Cons of Flat File Databases?

Data redundancy and inconsistency, Reduced data intergrity, Data dependence and Quiries and reports are more challengin in a flat-file.

What are the rules a database should abide by?

Each column must only contain one data type, One column, or a combination or columns, must make each row unique, no two rows can be the same

What is the unique identifier for a column of combination of columns?

The unique identifier is called a primary key, if several columns are used it is called a composite primary key

How are tables in a relational database linked?

They are linked by relationships.

How is a relationship between two records produced?

By setting the value of a foreign key field to that of the primary key of the record in another table

What are the benefits of normalisation?

no data redundancy, data intergrity is maintained, referential intergrity, faster searching and more complex queries can be used.

What are the four stages of normalisation?

Unnormalised form, First normal form, second normal form and third normal form

how do you convert from UNF to 1UF?

Eliminate duplicate columns from the same table, create separate tables for each group data, indetify a column which will identify each row.

How do you convert from 1NF to 2NF?

Remove any datasets occurring in multiple rows and trasfer them to new tables can create relationships between these new tables with foreign

how do you convert from 2NF form to 3NF?

Remove any columns which are not dependent on the primary key

What does a database deal with?

database structure, individual tables queries, interfaces, views and outputs

What are the proactive maintenance roles the DBMS deals with?

the setup and maintenance of access rights, automating backups, preserving referentail intergrity, maintaining indexes and updating the data

how does the DBMS ensure referentail intergrity?

the DBMS ensures foreign keys correspond to hte primary key of a record in the linked table.

how does the DBMS ensure that every foreign key corresponds to a primary key?

Preventing records from being deleted if they are referenced by records in other tables or deleting records referencing that is deleted

What are the different types of database veiws?

Physical View, Logical View and User View

what would "Select name, city from customers;" do?

Returns the value of name and city columns for each record in the customers table

What would "SELECT * FROM customers WHERE country = "Mexico";" do?

Returns all columns for records in the customers table where the country field is Mexico.

What would "SELECT * FROM customers WHERE country = "Germany" and city = "Berlin";" do?

Returns all columns for records in the customers table where the country field is Germany and city is Berlin.

What would "SELECT * FROM customers WHERE city = "Berlin" or city = "Munich";" do?

Returns all columns for records in the customers table where the city field is either Berlin or Munich.

What would "DELETE FROM customers WHERE name = "Jordan";" do?

Deletes all records from the customers table where the name field is Jordan.

What would "INSERT INTO customers (name, country, city) VALUES ("Matt","England","London");" do?

Inserts a new record into the customers table with name of Matt, county England and city London.

What would "DROP TABLE customers;" ?

Deletes the customers table and all its records.

What would "SELECT name, cost FROM customers JOIN orders ON customers.id = order.customer_id;" do?

Returns the customer name and order cost for all records in the orders table.

What are the four rules within ACID?

Atomicity, Consistency, Isoltion and Durability

What are the four basic functions within CRUD?

Create, Read, Update and Delete.