Databases

Question 1

Entity-Relationship Modelling

Answer 1

A method of abstractly describing the data tables and the relationships between them visually. They can be used to reduce redundancy and construct a relational database.

Question 2

What is an entity?

Answer 2

It is a thing about which data is to be stored, for example: a customer.

Question 3

What is an attribute?

Answer 3

Attributes are characteristics or other information about entities, for example: the customer’s name or address.

Question 4

What is a relational database?

Answer 4

It is a type of database that stores and provides access to data points that are related to one another.

Question 5

What is a primary key?

Answer 5

A primary key is an attribute that provides an unique identifies for every entity in a database table.

Question 6

What is a composite key?

Answer 6

If it is not possible to form a primary key from just one attribute. It is possible to combine attributes to form what is called a composite primary key

Question 7

What is a foreign key?

Answer 7

A foreign key is an attribute in a table which is the primary key in another, related, table.

Question 8

What is a one-to-one relationship?

Answer 8

Where one entity could be linked to only one of another entity

Question 9

What is a one-to-many relationship?

Answer 9

Where one field from a table is linked to several records from another table.

Question 10

What is a many-to-many relationship?

Answer 10

For example, a tutor might have multiple students and students might have multiple tutors. This type of relationship can become complicated when turned into a database because they cannot occur in real life; data cannot be transferred as it logically doesn’t make sense. This can be fixed using an association table. For example, a tutor can publish many papers and a paper can have many publishers so the association is Publication Issue Number; this is because it’s the simplest link.

Question 11

What is normalisation?

Answer 11

Normalisation is the process of removing redundancy within a database. Doing so makes accessing and storing data easier and more efficient

Question 12

Steps for Un-normalised Form

Answer 12

1. Select an initial key. This acts as a starter key. It needs to be unique so you can derive other values from it. If there is no suitable key, add one.
2. Transfer all identifiable attributes, ensuring each has their own relevant and unique name.
3. Look for repeating groups. These are a group of similar attributes that have multiple values for a single value of the initial key. Select a suitable initial key for the repeating group, surround them with brackets, and write them apart from the single-valued attributes

Question 13

Steps for First Normal Form

Answer 13

1. Create new relations by separating all repeating groups select a new primary key for the new relation and propagate the initial key to form a new composite key.
2. All other single-values attributes remain with the initial key

Question 14

Steps for Second Normal Form

Answer 14

The second stage of normalisation requires that all key attributes in a table are dependent on each other and that the table is in first normal form. Only then can you move on to creating a database that is in second normal form which will provide even more strength to the data structure.

1. Separate any attributes from keys formed in the previous step that are only dependent on one part of the composite key

Question 15

Steps for Third Normal Form

Answer 15

The third step is the transition into third normal form.

1. Separate any attributes that are dependent on other non-key attributes; foreign keys are retained in the original relation
2. Check composite keys for redundant parts. If a part of a key can be derived from other attributes, demote key attribute to non-key

Question 15

Advantages of normalisation

Answer 15

no redundancy, consistent data throughout linked tables, records can be added and removed without issues, complex queries can be carried out

Question 16

What is SQL?

Answer 16

Structured Query Language is a declarative language used for querying and updating tables in a relational database.

Question 17

What are the two types of SQL?

Answer 17

Data Definition Language, Data Manipulation Language

Question 18

What is DDL?

Answer 18

The data definition language is a language used to build the structure of a database, and consists of a variety of commands which allow the user to define the structure of the database by creating, altering or deleting tables. It also allowed the administrator to apply constraints to a database table.

Question 19

What is DML?

Answer 19

The data manipulation language is the language used to populate and update the database after it has been created. DML allows the user to insert, modify, delete, and query a database table for the data values of the entire table, for a specific record or for records that meet criteria.

Question 20

VARCHAR(n)

Answer 20

Variable length string of maximum n characters

Question 21

CHAR(n)

Answer 21

Fixed length string of n characters

Question 22

DATE

Answer 22

A date (there is also TIME, TIMESTAMP, and DATETIME)

Question 23

INT

Answer 23

Whole numbers (positive and negative)

Question 24

BOOLEAN

Answer 24

TRUE/FALSE

Question 25

DECIMAL (n,d)

Answer 25

Fixed-point decimal number with n digits in total, d is the number of digits after the decimal point

Question 26

DOUBLE (n,d)

Answer 26

Floating-point decimal number with n digits in total, d is the number of digits after the decimal point

Question 27

ENUM

Answer 27

List of permitted values (encoded as numbers)

Question 28

BLOB

Answer 28

Binary data (such as an image)

Question 29

TEXT

Answer 29

For large quantities of text

Question 30

How do you create a table?

Answer 30

CREATE TABLE tablename(
Entity1 DATATYPE(n),)

Question 31

How do you define a primary key?

Answer 31

PRIMARY KEY (MemberId, CourseCode)

Question 32

How do you define a foreign key?

Answer 32

FOREIGN KEY (name) REFERNCES

Question 33

How do you alter a table structure?

Answer 33

Used to add, delete or modify columns in an existing table:

ALTER TABLE tablename
ADD nnnn INTEGER

Question 34

Delete or alter a table structure

Answer 34

To delete a column
ALTER TABLE tablename
DROP mmmm

TO change the data type of a column:
ALTER TABLE table name
MODIFY COLUMN n DATAYPE(n) NOT NULL

Question 35

How do you SELECT FROM WHERE ORDER BY

Answer 35

SELECT productID, productName, subject, price
FROM tblProduct
WHERE level = 4
ORDER BY productNAME

Question 36

Updating data

Answer 36

UPDATE Product
SET Description = “mmlMK”, Price = “njnomom”
WHERE ProductID = “A345”

Question 37

Deleting

Answer 37

DELETE FROM Product
WHERE ProductID = “A345”

Question 38

What are client server databases?

Answer 38

The client can be a node on a network, an individual computer or someone sat on the opposite site of the planet and the structure still works the same. the idea is that a client requests data from another program (the server) by a connection made over a wide area network or over a local area network. Once the data request is fulfilled, the connection is terminated.

Question 39

Advantages of client-server

Answer 39

Centralisation - allows server admins to decide the inf