Database Theory

Question 1

Normalization is the process of ________________.

Answer 1

Normalization is the process of efficiently organizing data in a database.

Question 2

There are a few (4) goals of the normalization process:

Answer 2

There are a few (4) goals of the normalization process:

1) Eliminate redundant data (for example, storing the same data in more than one table)
2) Ensuring data dependencies make sense (only storing related data in a table)
3) Eliminate anomalies (Operations on a database table should not affect the validity of data of other entities)
4) Eliminate derived data. (Information that could be calculated from should not be stored)

Question 3

Normalization reduces the _____________ consumes and ensures that data is ______ ______.

Answer 3

Normalization reduces the amount of space a database consumes and ensures that data is logically stored.

It’s important to point out that they are guidelines and guidelines only. Occasionally, it becomes necessary to stray from them to meet practical business requirements.

Question 4

You shouldn’t have the same information in full text form in _____ ______ if possible.

Answer 4

You shouldn’t have the same information in full text form in multiple tables if possible.

If Dan were to change his name you would have to go through multiple tables and change them all.

Question 5

Generally, when you have tables with a lot of redundant data, it needs to be _________.

Answer 5

Generally, when you have tables with a lot of redundant data, it needs to be broken into multiple tables.

Question 6

The college address should not be put into this Student table due the fact it could change and you would have to go into each record and change it. Each table should have data that generally is very specific to itself.

Answer 6

The college address should not be put into this table due the fact it could change and you would have to go into each record and change it. Each table should have data that generally is very specific to itself.

Question 7

The ProductTotal field should not be entered since it can be _______ .

Answer 7

The ProductTotal field should not be entered since it can be calculated.

Question 8

Every student has a primary mobile telephone number (probably!), and every mobile telephone number corresponds to just one person. There is a _____ relationship between students and mobile telephone numbers.

Answer 8

Every student has a primary mobile telephone number (probably!), and every mobile telephone number corresponds to just one person. There is a one-to-one relationship between students and mobile telephone numbers.

If we have a table whose entity is Student (i.e. a table with information about students), we could simply add “Mobile number” as one of the fields in that table. However, we might not want to clutter the table with this kind of information, so we might make another table showing simply the student ID and the telephone number. We can then look up this information if we ever need it. The link between the two tables Students and Mobiles would be a one-to-one link.

Question 9

Other examples of one to one relationships:

Answer 9

Other examples of one to one relationships:

1) One student in IMM has one computer
2) One faculty member is assigned one parking spots

Question 10

A student only has one Director of Studies in any one year (usually), but a Director of Studies can have many different students.

The relationship between Directors of Studies and Students is a _______ relationship. So, if our database were to show the Director of Studies of each student, we could have a table listing all the Directors of Studies of the Colleges, with their IDs (primary key) and any extra information wanted (full names and contact information, for example).

DoS_ID Name College Telephone
rrb20 Zen, Dan Sheridan 905-555-0000

Answer 10

A student only has one Director of Studies in any one year (usually), but a Director of Studies can have many different students. The relationship between Directors of Studies and Students is a one-to-many relationship. So, if our database were to show the Director of Studies of each student, we could have a table listing all the Directors of Studies of the Colleges, with their IDs (primary key) and any extra information wanted (full names and contact information, for example).

You could then simply add a field “DoS” to your Students table (since a student can only have one DoS) containing the DoS’s ID, and link the two tables, DoS and Students, with a one-to-many relationship. The linked fields are the DoS_IDs (which appear in both tables).

Question 11

Other examples of one to many relationships:

1)
2)

Answer 11

Other examples of one to many relationships:

1) Many students have one Program (IMM)
2) Many students in IMM are assigned to one class room(s139) for learning

Question 12

(Note: Supervisor could also be Teacher) There is a kind of relationship that needs special handling in ______ databases, the many-to-many relationship.

One student may have many supervisors, but equally, one supervisor will have many students. This poses a problem in terms of how to represent the relationship without resorting to repeating attributes

Answer 12

(Note: Supervisor could also be Teacher) There is a kind of relationship that needs special handling in relational databases, the many-to-many relationship.

One student may have many supervisors, but equally, one supervisor will have many students. This poses a problem in terms of how to represent the relationship without resorting to repeating attributes

Question 13

If you find yourself wanting to put repeating attributes in a table, then it is a sure sign that there is something wrong with your ____ ________.

Imagine the complications here if the Supervisors table were to list all the students taught by each supervisor: you would have to have an indeterminate number of fields: Student1, Student2, Student3, Student4, …, Student25 …

Solution?

Answer 13

The solution is to provide a third linking table, one which simply lists pairs of supervisors and supervisees.

In relational databases, many-to-many relationships always require a third linking table between the two entities which are linked by this kind of relationship.

Question 14

Note that it doesn’t matter if a student ID or a supervisor ID appears ______, in fact that’s the whole point since a student can have many supervisors and vice versa.

There is also no record that is EXACTLY like another in this table. This table doesn’t need a ______ _____ because the pairs of IDs together each form a unique composite key.

Answer 14

Note that it doesn’t matter if a student ID or a supervisor ID appears twice, in fact that’s the whole point since a student can have many supervisors and vice versa.

There is also no record that is EXACTLY like another in this table. This table doesn’t need a primary key because the pairs of IDs together each form a unique composite key.

Question 15

A field that uniquely identifies a record in a table.

In a students table, for instance, a key built from last name + first name might not give you a unique identifier (two or more Jane Does in the school, for example).

To uniquely identify each student, you might add a special Student ID field to be used as the _______ key.

Answer 15

A field that uniquely identifies a record in a table.

In a students table, for instance, a key built from last name + first name might not give you a unique identifier (two or more Jane Does in the school, for example).

To uniquely identify each student, you might add a special Student ID field to be used as the primary key.

Question 16

A key used in one table to represent the value of a primary key in a related table.

While ____ keys must contain unique values, _____ keys may have duplicates.

For instance, if we use student ID as the primary key in a Students table (each student has a unique ID), we could use student ID as a _____ key in a Courses table: as each student may do more than one course, the student ID field in the Courses table (often shortened to Courses.student ID) will hold duplicate values.

Answer 16

A key used in one table to represent the value of a primary key in a related table.

While primary keys must contain unique values, foreign keys may have duplicates.

For instance, if we use student ID as the primary key in a Students table (each student has a unique ID), we could use student ID as a foreign key in a Courses table: as each student may do more than one course, the student ID field in the Courses table (often shortened to Courses.student ID) will hold duplicate values.

Question 17

____[(_)] A normal-size integer.

Answer 17

INT[(M)] A normal-size integer.

The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

Question 18

_______(_) [ A variable-length string.

___ represents the maximum column length in characters.

Answer 18

VARCHAR(M) [ A variable-length string.

M represents the maximum column length in characters.

In MySQL 5.0, the range of M is 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in MySQL 5.0.3 and later.

The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

Question 19

_____(_) [ A fixed-length string.

__ represents the exact column length in characters.

Answer 19

CHAR(M) [ A fixed-length string.

M represents the exact column length in characters.

In MySQL 5.0, the range of M is 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in MySQL 5.0.3 and later.

Question 20

___[(_)] A TEXT column with a maximum length of 65,535 (216 – 1) characters.

Answer 20

TEXT[(M)] A TEXT column with a maximum length of 65,535 (216 – 1) characters.

An optional length M can be given for this type. If this is done, MySQL creates the column as the smallest TEXT type large enough to hold values M characters long

Question 21

BLOB A BLOB is a _______ _____ object that can hold a variable amount of data.

It could be an image, sound,etc.

The four BLOB types are:

Answer 21

BLOB A BLOB is a binary large object that can hold a variable amount of data.

It could be an image, sound,etc.

The four BLOB types are:
TINYBLOB(255b), 
BLOB(65k), 
MEDIUMBLOB(16mb), and 
LONGBLOB4(gb). 

These differ only in the maximum length of the values they can hold.

Question 22

DATE A date. The supported range is ‘1000-01-01’ to ‘9999-12-31’.

MySQL displays DATE values in ‘YYYY-_ - _’ format, but allows assignment of values to DATE columns using either strings or numbers

Answer 22

DATE A date. The supported range is ‘1000-01-01’ to ‘9999-12-31’.

MySQL displays DATE values in ‘YYYY-MM-DD’ format, but allows assignment of values to DATE columns using either strings or numbers

Question 23

SQL is short for ______ ______ ______ and is a widely used _______ language, providing means of data ________ (_____ ____, _____, _____) and database ________.

Answer 23

SQL is short for Structured Query Language and is a widely used database language, providing means of data manipulation (store, retrieve, update, delete) and database creation.

Question 24

A _____ statement is executed when you wish to retrieve information from a database.

This statement contains the instructions on what information to _____. The information is ______ in a dataset, which can be thought of as a virtual table that just stores the results from the _______statement.

Answer 24

A select statement is executed when you wish to retrieve information from a database.

SELECT * FROM Table1

This statement contains the instructions on what information to return. The information is returned in a dataset, which can be thought of as a virtual table that just stores the results from the select statement.

Question 25

The SQL _______ clause is used to select data conditionally, by adding it to already existing SQL SELECT query. Note the quotes (remember strings)

Answer 25

The SQL WHERE clause is used to select data conditionally, by adding it to already existing SQL SELECT query. Note the quotes (remember strings)

SELECT Column1, Column2, Column3 FROM Table1

Question 26

To add a record to our database you must use the _____ statement.

The SQL _____ INTO syntax has 2 main forms and the result of either of them is adding a new row into the database table.

Answer 26

To add a record to our database you must use the INSERT statement.

The SQL INSERT INTO syntax has 2 main forms and the result of either of them is adding a new row into the database table.

INSERT INTO Table1 VALUES (value1, value2, value3…)

Question 27

The SQL ______ clause changes the data in already existing database row(s) and usually we need to add a conditional SQL ______ clause to our SQL ______ statement in order to specify which row(s) we intend to update.

If we do not add the _____ clause, all records in the specified table will be updated.

Answer 27

The SQL UPDATE clause changes the data in already existing database row(s) and usually we need to add a conditional SQL WHERE clause to our SQL UPDATE statement in order to specify which row(s) we intend to update.

If we do not add the WHERE clause, all records in the specified table will be updated

Question 28

The _______ command will delete specified records.

If you skip the SQL WHERE clause when executing SQL _____ expression, then all the data in the specified table will be deleted.

Answer 28

The delete command will delete specified records.

If you skip the SQL WHERE clause when executing SQL DELETE expression, then all the data in the specified table will be deleted.

DELETE FROM Table1 WHERE Some_Column = Some_Value

Question 29

When data is required from __ or more tables, the _____ statement can be used.

The tables must that have a field that exists in both tables. This is how we can get data about two or more different entities using SQL.

Answer 29

When data is required from 2 or more tables, the JOIN statement can be used. The tables must that have a field that exists in both tables. This is how we can get data about two or more different entities using SQL.

Question 30

An ____ or _____ ____ selects data from 2 tables.

Answer 30

An JOIN or INNER JOIN selects data from 2 tables.

Question 31

A LEFT JOIN selects data only from the “Table_” table but still joins on Table_.

Answer 31

A LEFT JOIN selects data only from the “Table1” table but still joins on Table2.

Only data found in Table1’s JOIN field and Table2’s JOIN field is returned.

Question 32

A RIGHT JOIN selects data only from the “Table_” table but still joins on Table_.

Answer 32

A RIGHT JOIN selects data only from the “Table2” table but still joins on Table1.

Only data found in Table1’s JOIN field and Table2’s JOIN field is returned.

Question 33

The following should be encased in backticks:

1) _____ names
2) _____ names
3) _____ names

Answer 33

The following should be encased in back-ticks: ` ` =

1) Database names
2) Table names
3) Field names

Question 34

The following should be encased in single quotes:

______ values such as:
1)
2)
3)

Answer 34

The following should be encased in single quotes:

String values such as:

1) char
2) text
3) varchar values

Question 35

Numeric values do not need to be encased in ______.

Answer 35

Numeric values do not need to be encased in quotes.

Question 36

A semicolon _____ an SQL statement.

Answer 36

A semicolon ends an SQL statement.

You can execute multiple SQL statements by separating them with semicolons.

Question 37

In order to setup a database for use, follow these steps:

5 Steps:

Answer 37

In order to setup a database for use, follow these steps:

1) Navigate to MySQL databases in CPanel
2) Create a new database by giving it a name and click on “Create Database”
3) Next, Add a new user with a secure password. Keep this password in a safe place
4) Add that user to the database.
5) Set the necessary privileges on the user

Question 38

In order to setup a database for use, follow these steps:

(Your PHP scripts will later use the database name, username and password to access the database.)

6 More Steps:

Answer 38

Your PHP scripts will later use the database name, username and password to access the database.

1) Next, open PHPMyAdmin
2) You should see your database name on the left hand column
3) Create and configure your tables
4) For security and consistency purposes, please ensure that your character set is utf8mb4
5) Configure primary key and AutoIncrement (A_I) for primary key fields as needed
6) Use PHPMyAdmin to manage your data, either via the PHPMyAdmin user interface or the built-in SQL command interface