Question

QUESTION 1

1. Which of the following statement is CORRECT about normalisation?

   		A database that is not in at least third normal form cannot be implemented with MySQL.
   		A database that is only in first normal form would breach the CAP theorem.
   		Normalisation ensures consistent formatting, for example, that all the phone numbers would have the same number of digits.
   		All of the above.
   		None of the above.

0.5 points   

QUESTION 2

1. Complete the four blanks in the following SQL query to retrieve all information from the table Customers where birthYear is greater than (or equal to) 1970 but less than (or equal to) 2005. Your answer will be marked incorrect if there is a spelling mistake, missing spaces, or unnecessary spaces.

   SELECT
   FROM
   WHERE  BETWEEN ;

1 points   

QUESTION 3

1. When normalising from first to third normal form, we would typically have:

   		more relations
   		less relations
   		more data stored
   		less data stored
   		none of the above

0.5 points   

QUESTION 4

1. Complete the table below by matching each definition with the appropriate SQL constraint.

   -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    A mandatory clause that is at the start of all SQL retrieval queries       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    A clause used to return only the values in a result table that are unique       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    A clause used to filter rows according to some condition or predicate       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    A clause that is used to list the tables and any joins required in a query       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    An operator that allows multiple values to be specified in a comparison statement       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    An operator used to assign an alias to a table or column       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    If used, it must be the last clause in a query       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    Allows rows which have the same value for one or more columns to be grouped together and treated as a unit when using aggregate functions       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    A clause which is used to filter aggregate values in the final result table       -       A.       B.       C.       D.       E.       F.       G.       H.       I.       J.       K.       L.       M.       N.    An operator used to create a table with all rows from two tables

   A. IN B. ON C. UNION D. AS E. FROM F. SELECT G. WHERE H. SELECT DISTINCT I. NATURAL JOIN J. ORDER BY K. GROUP BY L. LIKE M. INNER JOIN N. HAVING

Answer 1

Answer 1

The Correct answer is None of the above.

Explanation:

A database that is not in at least third normal form can be implemented with MySQL.

Validation ensures consistent formatting, for example that all the phone numbers would have the same number of digits.

CAP - consistency , availability and partition tolerance. It states that it is not possible to achieve all three properties for data.

Answer 2

The BETWEEN clause will include all values between 1970 and 2005 where 1970 and 2005 are inclusive.

SELECT *

FROM Customers

WHERE birthYear BETWEEN 1970 and 2005 ;

Answer 3

Normalization is used to reduce duplicate data and minimize anomalies in relations.

For normalising tables we use 3 normal forms and when we apply these normal forms, we divide tables into new tables and link them together. So when normalising from 1NF to 3 NF data increase so we have more tables and more relations.

Answer 4

A mandatory clause that is at the start of all SQL retrieval queries – SELECT

A clause used to return only the values in a result table that are unique -SELECT DISTINCT

A clause used to filter rows according to some condition or predicate – WHERE, ORDER BY

A clause that is used to list the tables and any joins required in a query - NATURAL JOIN

An operator that allows multiple values to be specified in a comparison statement – IN, LIKE

An operator used to assign an alias to a table or column – AS

If used, it must be the last clause in a query – HAVING

Allows rows which have the same value for one or more columns to be grouped together and treated as a unit when using aggregate functions – GROUP BY

A clause which is used to filter aggregate values in the final result table – ON

An operator used to create a table with all rows from two tables - UNION

if you like the answer please give me a thumbs up.