Databases

Question 1

What is a database?

Answer 1

• A structured persistent store of data. Stored on the secondary storage devices of a computer system
• Provide security, auto backups and users with controlled access to data they need

Question 2

Benefits of databases?

Answer 2

-Make it fast to: access data, update data, search for data and present data in a suitable way

Question 2

What are the layers of a Database?

Answer 2

• External level: What the user sees. Acts like a filter on data in the database Designed to be useful for a particular job. User friendly UI. Hides unnecessary/restricted data. Allows user to interact with DB
• Conceptual view: Describes structure of DB, entities, attributes, data types, relationships, validation, tables. Hides details of physical storage structures
• Physical: How the data is actually stored in the DB, works with OS and DBMS to store and retrieve data from storage devices

Question 3

What is data security?

Answer 3

• Keeping data safe from accidental or deliberate loss and malicious attacks.
• Controls Who has access to the database and what they are permitted to do

Question 4

What are files?

Answer 4

• Data stores on a computers secondary storage medium that have names and are stores of binary data
• Can be a program, word processed document, image or music

Question 5

What are serial data files?

Answer 5

• Records organised one after another
• Only possible way to storage files on mediums such as tape
• Each data file is as large as the data to be stored
• No empty records = efficient
• Slow as it has to read through each data record until relevant record is found
• If data is modified then the complete altered file has to be re-written back to storage medium. Temp file created and replaces original until process is complete

Question 6

What are sequential data files?

Answer 6

• Same as serial but records are stored in order of a designated key field (Eg: alphabetical)
• Records can be found easier
• Data modification still raises same issues as serial
• Not great for data that has no particular order as data has to be sorted often

Question 7

What is indexing?

Answer 7

• Speed up searches
• Software searches for the start of the right section (like a book chapter), then directs the search to the right part of the main data store. After that a sequential search can look for the item required
• Records match length of data to be stored
• No wasted space
• Deleting a record will only delete index number, data still remains
• New records are added to end of files and index number is increased
• Eventually DB will have large amounts of wasteful data that is not indexed. Can be solved by rewriting the file and therefore removing any un-indexed data and re-indexing the rest

Question 8

What is an indexed sequential data file?

Answer 8

• Used for large Sequential Data Files

- Stored in order of key field AND index

Question 9

What is a random access data file?

Answer 9

• Each record is ID with a record number in sequence and each record occupies same space
• Quick access to data as it can calculate position based on record number
• Wasteful of storage as EVERYTHING is stored with same amount of memory
• Each time new file is added it is added to the end and the RN is Incremented
• When records are removed only the data is deleted, leaving an empty record. Overtime there can be many blank records

Question 10

What are fixed length fields?

Answer 10

• Suitable amount of space In a file is reserved for data
• Any unused bytes are packed with a character such as a space
• Less space efficient however processing is more straightforward because bytes can be counted to reach desired point. (After set ‘n’ number of bytes you will move onto next field)
• Quick to search

Question 11

What are variable length fields?

Answer 11

• Only store as much data as is needed. Eg “Fred’ takes 4 bytes and ‘Jennifer’ takes 8 bytes
• Space efficient
• Processing is harder as finding a particular record requires checking all the preceding entries sequentially

Question 12

What is a flat file database?

Answer 12

• Simple databases with one table
• A single spreadsheet is an example
• used for simple data-handling requirements

Question 13

What is a relational database?

Answer 13

• Multiple related tables
• Each table represents a single entity (real world object or person)
• Required for the storage of more complex data
• Tables are linked via common attributes to create relationships (Primary keys —> foreign keys)
• Reduces amount of duplicated data
• Entity is anything about which we store data. They have attributes (characteristics eg: student name, ID, subject level)
• Tables consist of Tuples (records)

Question 14

What is a hierarchal database?

Answer 14

• Multiple tables
• Tree like structure
• Each child node linked to parent node

Question 15

Structure of relational databases? (Including primary, secondary and foreign keys)

Answer 15

• Use fixed length fields
• Each column contains 1 data type
• No rule about order of rows in a table. When order is required indexes are added
• No rule about order of columns
• No two rows can be identical
• One column must be primary key therefore making each row unique
• Primary key is used to link a table to the foreign keys of other tables. Relationships link them together and are produced by having repeated fields across tables (foreign key)
• Secondary keys are not necessarily unique but can be useful for quick searching

Question 16

What are entity-relationship diagrams? Draw a one-to-many relationship, many-many relationship and one-one relationship

Answer 16

-A data modelling technique used to define a relational database
RELATIONSHIP DIAGRAMS CAN BE FOUND IN PHOTOS
-In relational databases we want tables to be related by one-to-many relationships
-A one-to-one relationship suggests that 2 entities should be in the same table
-A many-to-many relationship suggests the need for at least one more table to separate entities

Question 18

What is hashing?

Answer 18

• Method of transforming a string of characters in a record into a shortened form that can be used as a disk address
• This shortened form can be used to access a record from a database more easily than by using the complete original string
• Typically multiple records can produce the same hash values. In this case the data is located in the next available space on the storage medium, so some serial searching may be necessary

Question 21

What is data redundancy?

Answer 21

• Unnecessary duplication of data in database

- Repeated fields etc. We need some repeated fields to make links but to many leads to errors and wastes storage space

Question 22

What is referential integrity?

Answer 22

• one aspect of data integrity
• You cannot delete a record if it is linked to a record in another table
• a state the DB where inconsistent transactions are not possible
• eg; cannot enter a student for subject that doesn’t exist or not being able to delete a subject which a student is connected to

Question 24

What is a DBMS? What does it create and what does it provide?

Answer 24

• Database management system that creates and maintains a database. Eg: Microsoft Access
• Creates and uses: the structure, queries, views, individual tables, interfaces and outputs
• Provides: security, backups, index updating, enforcement of referential integrity and facilities to update and interrogate the DB

Question 25

What is transaction processing?

Answer 25

• attempts to provide a response to the user within a short time frame.
• not as time critical as real time
• features limited range of operations planned in advance, such as a bank account balance enquiry or withdrawal

Question 26

What is CRUD?

Answer 26

• relational databases must have certain basic functionality to be useful
• Create, Read, Update, Delete
• match to; INSERT, SELECT, UPDATE, DELETE. SQL commands
• 3 of the commands result in transaction taking place

Question 27

What is data integrity?

Answer 27

• transaction must not allow DB to be damaged else DB will be inconsistent
• DBMS ensures that DB changes from one consistent state to another

Question 28

What is data corruption?

Answer 28

- opposite of data integrity - caused by; hardware/software failures or electrical glitches - can also be operator error or malpractice

Question 29

What is ACID?

Answer 29

-protects integrity of a DB

Question 30

What does the A in ACID stand for?

Answer 30

-Atomicity = a change is either completely | performed or not at all

Question 31

What does the C in ACID stand for?

Answer 31

-Consistency = a transaction must take the whole DB from one consistent state to another (bank transaction; total money in system must remain the same)

Question 32

What does the I in ACID stand for?

Answer 32

- Isolation = a transaction must be performed in isolation so that the other processes/users cannot access that data until it is in a consistent sate - while an operation is being performed on a record, the record is locked

Question 33

What does the D in ACID stand for?

Answer 33

-Durability = once committed, a change must not be lost due to any subsequent system failures

Question 34

What is the aim of DB normalisation?

Answer 34

- makes a DB more efficient process by which a collection of data is gradually organised into tables through a series of steps - cumulative and the stages have to be worked through in succession - makes sure the data in each table is all properly and completely related together - attributes in a relation must not themselves be sets, multiple values for one tuple are not allowed

Question 35

What is the First Normal Form (1NF)?

Answer 35

1. Eliminate duplicated columns from same table 2. Create separate tables for each group of related data 3. ID a primary key - still not perfect as there are multiple repeating fields

Question 36

What is the Second Normal Form (2NF)?

Answer 36

1. Check data is in 1NF 2. Remove any data sets that occur in multiple rows and move them to new tables 6. Create relationships between these new tables and previous tables by means of foreign keys (primary key from other tables)

Question 37

What is Third Normal Form (3NF)?

Answer 37

1. check that the data is in 2NF 2. Remove any columns that are not dependent on the primary key - "every non-key attribute in a table must depend on the key, the whole key and nothing but the key"

Question 38

what are queries?

Answer 38

- main use of DB's - used to isolate and display a subset of the data in a database. -They can take related data from multiple tables and present them in an easy-to-understand way - used as the basis for a screen form of a printed report so that filtered data can be presented in some clear or standard way

Question 39

What is the main way queries are performed?

Answer 39

Query By example (QBE) comes with most off the shelf DBMSs. User is presented with GUI into which can be dropped the fields required as well as setting up conditions to filter the results

Question 40

What is SQL?

Answer 40

- the code behind queries that allow for more flexible ones to performed - Fields required are separated by commas . SELECT is used to extract the required data from the data set and conditions can be applied using the WHERE clause - Conditions can be tailored exactly to meet the operational requirements

Question 41

What happens if multiple tables are being queried in one SQL statement?

Answer 41

- dot notation is used - eg; if "customername" and "tableorder" are both being queried the fields will be written as "customername.name" and "tableorder.order"

Question 42

What are wild card operations?

Answer 42

- '*' means everything - '%' means one or many characters - '_' means just one character

Question 43

What else can SQL do?

Answer 43

- has features that allow the creation and modification of DB's - rich set of commands and operators that can perform any data processing required on a relational DB

Question 44

What are some other SQL commands?

Answer 44

- CREATE = allows creation of tables - INSERT = allows data to be entered in DB's - DROP = allows SQL program to remove indexes, tables, fields and whole databases (structural removal) - DELETE = allows the removal of data from a table (data removal) - JOIN = combines data from two or more tables using a duplicated field such as a customer number in both the customer table and the order table

Question 45

Practice SQL

Answer 45

Do it