Databases

Question 1

what is a database?

Answer 1

They are structured sets of data. They are organised in such a way that they can be easily managed, accessed, updated and analysed.
Eg. a relational database - made up of tables

Question 2

what is a flat file?

Answer 2

The simplest form of a database - it is a single file, normally organised in a table structure with rows and columns

Question 3

how would you write an entity description?

Answer 3

Entity1 (Attribute1, Attribute2, … )

Question 4

what is a relational database?

Answer 4

a set of tables whose records are linked by certain fields. Each table contains data on one entity. the tables can be linked together using foreign keys (a relationship)

Question 5

what is the purpose of an entity identifier?

Answer 5

it is used to uniquely identify the entity - it is the primary key in a relational database

Question 6

What is a record?

Answer 6

All the fields relevant to an entity - kind of like a row in a table

Question 7

what is an entity?

Answer 7

it is a category of object, person, event or thing of interest to an organisation, about which data is to be recorded

Question 8

what is a field/attribute?

Answer 8

it is a property or characteristic of an entity - it is kind of like a column in a table

Question 9

what is the primary key?

Answer 9

• it is an entity identifier that uniquely identifies records in an entity. Data in the entity will be sorted by this by default
• denoted by being underlined in the entity description: eg. Dentist (DentistID, Title, Firstname, Surname, Qualification)

Question 10

what is a composite primary key?

Answer 10

• its a primary key made up of multiple fields - used to maintain uniqueness
• eg. a customer order with multiple order lines would have OrderNumber + OrderLine as composite primary key

Question 11

what is a secondary key?

Answer 11

• any other fields in the table that aren’t the primary key
• makes sure that records in the database remain searchable since a customer/user may not know the relevant primary key value

Question 12

what is a foreign key?

Answer 12

• its an attribute that creates a join between tables
• the attribute is common to both tables
• foreign key in one table = primary key in the table its linked to
• denoted by being in italics - eg. Patient (PatientID, Title, Firstname, Surname, Address, Telephone, DentistID)

Question 13

How can entities be related + how are they represented in an entity relationship diagram?

Answer 13

One-to-one:
- eg. husband + wife, country + prime minister
- shown by 2 rectangles (for entities) joined by one straight line

One-to-many:
- eg. school + pupils, mother + child
- shown by 2 rectangles joined by a straight line on one side and birds legs on the other

Many-to-many:
- avoided where possible (eg, through normalisation)
- eg. actors + films, products + components, student + course
- represented by 2 rectangles joined by birds legs on either end

Question 14

what is an entity relationship diagram?

Answer 14

it is a diagramatic way of representing the relationships between entities in a database

Question 15

how would you remove a many-to-many relationship?

eg. you have 2 tables:
- student (studentID, name, address)
- course (courseID, subject, level)

Answer 15

create a middleman table eg:
- student (studentID, name, address)
- enrolment (_studentID_, _courseID_)
- course (courseID, subject, level)

Question 16

what is referential integrity?

Answer 16

no foreign key in one table can reference a non-existent record in another table - eg. can’t add a subscription for a customer if the customer ID doesnt exist in a record

Question 17

what is relational database structure?

Answer 17

• tables in the database = relations
• one database will have one or more relations
• relation has rows - aka records
• each record has fields/attributes - like columns

Question 18

how are databases indexed?

Answer 18

• records automatically indexed by primary key
• can also be indexed by any other secondary key (secondary index)

Question 19

what is normalisation?

Answer 19

• it is the process used to come up with the best possible database design
• 3 stages: 1st normal form (1NF), 2nd normal form (2NF), 3rd normal form (3NF)

Question 20

what should a normalised database look like?

Answer 20

• no data is unnecessarily duplicated
• data is consistent throughout the database
• table structure should be flexible enough for you to enter as many or as few items as required
• structure should allow as complex queries to be made

Question 21

what are the properties required of a database in first normal form?

Answer 21

• no repeating attributes/groups of attributes
• must have a primary key
• atomic - only one data item in one field

Question 22

how can you make a many to many relationship into 1NF?

Answer 22

use a link table/middleman table

Question 23

what are the features of a database in second normal form?

Answer 23

• already in 1NF
• no partial dependencies - one or more attributes depends on only part of the primary key (only works if it is a composite primary key)

Question 24

what are the features of a database in third normal form?

Answer 24

• already in 2NF
• contains no non-key dependencies - the vvalue of an attribute is determined by the value of another attribute which is not part of the key
• “all attributes are dependent on the key, the whole key, and nothing but the key”

Question 25

what are the advantages of a normalised database?

Answer 25

- no data redundancy (data that appears in more than one table) - less inefficinecy + inconsistencies in data - data integrity is maintained - no duplication of data + only need to modify 1 table if a change needs to be made - smaller tables with fewer fields = faster searching, sorting + indexing - saves storage space- data only held once - can't delete records accidentally on the one side of a one to many relationship

Question 26

what is SQL?

Answer 26

- stands for Structured Query Language - declarative language - used to query + update tables in a relational database

Question 27

how is data for a database captured?

Answer 27

- survey results may be manually inputted - magnetic ink character recognition (MICR) on bank cheques - optical mark recognition (OMR) used on multiple choice tests - Optical character recognition (OCR)

Question 28

how is data exchanged?

Answer 28

- using EDI - electronic data interchange - enables data transfer from one computer to another without using human interaction

Question 29

what is the SELECT statement used for?

Answer 29

to extract a collection of fields from a given table(s)

Question 30

what is the syntax for the SELECT statement?

Answer 30

`SELECT` (the fields to be displayed - * if all) `FROM` (list the table(s) data will come from) `WHERE` (search criteria, the conditions) `ORDER BY` (list the fields that results are to be sorted on, ascending by default)

Question 31

what is the wild card operator syntax?

Answer 31

`LIKE "comp%"` the percentage is the important bit - looks for anything in the database starting with comp followed by anything

Question 32

in the WHERE clause, how do you determine a range?

Answer 32

`BETWEEN ... AND` including the 2 values that define the limit

Question 33

in the WHERE clause, what does the IN operator do?

Answer 33

specify multiple possible specific values for a column for the record to be considered

Question 34

how do you reference an empty field?

Answer 34

`IS NULL`

Question 35

how do you write a data in SQL?

Answer 35

`#DD/MM/YYYY#` you need to enclose it in the hashes

Question 36

when are semicolons used?

Answer 36

at the end of each SQL statement - the standard way to separate them DONT put it within a statement

Question 37

what orders can be specified using ORDER BY?

Answer 37

`ASC` - ascending `DESC` - descending

Question 38

what is the syntax used to extract and combine data from 2 or more tables (without using join)?

Answer 38

```SELECT table1.fieldName, table2.fieldName, ... FROM table1, table2, ... WHERE (table1.fieldName1 = table2.fieldName1)```

Question 39

what is the syntax used to extract and combine data from 2 or more tables (using join)?

Answer 39

```SELECT table1.fieldName, table2.fieldName, ... FROM table1 JOIN table2 ON table1.fieldName1 = table2.fieldName1 WHERE ...```

Question 40

how do you define a table using SQL?

Answer 40

using `CREATE TABLE` ```CREATE TABLE ( column1 DATATYPE, column2 DATATYPE, column3 DATATYPE, ... )```

Question 41

what are the most commonly used data types when creating a table with SQL?

Answer 41

- `CHAR(n)` - character string of fixed length n - `VARCHAR(n)` - character string of variable length, max n - `BOOLEAN` - TRUE or FALSE - `INTEGER, INT` - integer - `FLOAT(a,b)` - number with floating decimal point - a = max no of digits before decimal point, b = max no of digits after decimal point - `DATE` - stores day, month, year values - `TIME` - stores hour, minute, second values - `CURRENCY` - formats numbers in currency used in your region

Question 42

how to add a column using SQL?

Answer 42

```ALTER TABLE table ADD column1 DATATYPE```

Question 43

how to delete a column using SQL?

Answer 43

```ALTER TABLE table DROP COLUMN column1```

Question 44

how to change data type of a column in SQL?

Answer 44

```ALTER TABLE table MODIFY COLUMN column1 NEWDATATYPE```

Question 45

how to create a foreign key using SQL?

Answer 45

done during CREATE TABLE ```FOREIGN KEY column1 REFERENCES table2(column1)```

Question 46

how to set the primary key in SQL?

Answer 46

done during CREATE TABLE: ```PRIMARY KEY (column1, ...)```

Question 47

how to add a record to a table in SQL?

Answer 47

```INSERT INTO table1 (column1, column2 ...) VALUES (value1, value2 ...)```

Question 48

how to update a record in a table in SQL?

Answer 48

```UPDATE table1 SET column1 = value1, column2 = value2, ... WHERE columnX = value```

Question 49

how to delete a record in a table in SQL?

Answer 49

```DELETE FROM table1 WHERE columnX = value```