1. What is the purpose of having foreign key(s) in a relational database table?
i. Represent a relationship between tables
ii. Foreign key in one table has the same value, referencing another table’s primary key iii. Referential integrity
2. What is the purpose of having a primary key in the relational database table?
i. Primary key is used as a unique identifier to represent the data in that row. ii. It is also used to provide a link to related tables (by being used as a foreign key). iii. The primary key MUST determine all non-key attributes.
3. How are relationships between tables expressed in a relational database?
i. Use of embedded foreign keys
ii. Use of a separate relation table
4. When do you have to use an “intersection entity” and what is it?
“Intersection entity” is an entity used in order to resolve M to M relationships. M-M relationships can be converted to a pair of one to many relationships by creating an intersection entity that contains one occurrence for each related pair. In general it means that a primary key from each entity becomes a part of the composite key for the intersection entity.
5. Briefly explain what would be main advantages of your newly created database system over an existing spreadsheet?
i. Avoids update, insert and delete anomalies
ii. Avoids data redundancy
iii. Better design structure, security, data independence
6. Database Management Systems contains two main parts in a database: data and meta-data. What is meta-data?
Data only becomes useful when placed in some context. The primary mechanism for providing context for data is meta-data. Mata-data is data about data. It describes the properties or characteristics of other data. Some of these properties include data definitions, data structures and rules or constraints.
7. What is a Database?
A database is a structure that can store information about multiple types of entities, the attributes of those entities, and the relationships among the entities.
8. What is a DBMS?
DBMS stands for Database Management Systems.
i. programs that enable the creation and maintenance of databases ii. construct and manipulate one or more databases
iii. functions including transaction processing, control of access and recovery
9. What are the advantages of a DBMS?
• reduce redundancies and errors within the data
• easier to extract information
• the system is more flexible
• security is improved
• integrity can be improved
10. What are the disadvantages of DBMS?
• takes longer to access data
• need special knowledge
• applications may be dependant to specific DBMS versions • initial cost may be large
11. What is normalization in database management systems? What is the purpose of normalization?
A technique for producing a set of relations with desirable properties, given the data requirements of an enterprise. • The minimal number of attributes necessary to support the data requirements of the enterprise • Attributes with a close logical relationship are found in the same relation • Minimal redundancy with each attribute represented only once with the important exception of attributes that form all or part of foreign keys • Reduce data redundancy and update anomalies including insertion anomalies, delete anomalies and insert anomalies.
12. What are 1NF, 2NF and 3NF?
• A relation is in 1NF if all value are atomic and no rows are repeated • A relation is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the primary key • A relation is in 3NF if it is in 2NF and every non-key attribute is non-transitively dependent on the primary key