Review Questions Lesson 02 Comp 1630 2r1zq

COMP 1630 Relational Database Design and SQL Review Questions Lesson 2

Answer each of the following questions labeling your answers clearly. Save your work in the Desire2Learn Drop box for Lesson 02 – Review Questions.

1. What is a business rule, and what is its purpose in data modeling? Is a brief, precise, and unambiguous description of a policy procedure, or principle within an organization. Business rules apply to any organization, large or small-a business, a government unit, a religious group, or a research laboratory-that stores and uses data, attributes, to generate information. For example, a pilot cannot be on duty for more than 10 hours during a 24-hour period, or a teacher may teach up to four classes during a semester The purpose of business rules set the stage for the proper identification of entities, attributes, relationships, and constraints. 2. How do you translate business rules into data model components? The business rules allow us to discern the different entities in the model and the relation to each other. It is for this reason that the type of relationship can be determined, whether bidirectional or not, ensuring that the relationships established between the entities comply with the business rules. As a rule a nouns translate into entities, the verbs translate into relationships betwen entities, those relationships are bidirectional. For example, the business rules “a Teacher may teach many classes” contains two nouns (teacher and classes) and a verb (“teach”) that associates those entities. 3. Explain how the entity relationship (ER) model helped produce a more structured relational The complex design activities require conceptual simplicity to produce successful results, so the database designers preferred to use a graphic tool in which they represent the entities and their relationships. Therefore, the relationship model of the entity (ER), or ERM, has become a widely accepted standard for data modeling. You can make a graphic representation of the entities and their relationships in a database structure. This became popular because it complemented the concepts of the relational data model. These representations

1

to model database components are easier to use and have a series of welldefined steps to generate all the required database structures 4. What is logical independence? A condition in which the internal model can be changed without affecting the conceptual model. (The internal model is hardware- independent because it is unaffected by the computer on which the software is installed Therefore, a change in storage devices or operating systems will not affect the internal model.) 5. What is a relational diagram? It is a representation of the entities of the relational database and the attributes within those entities these relationships can be of different types (1: 1, 1: M, M: N). 6. What is physical independence? Physical independence happens when A condition in which the physical model can be change without affecting the internal model. A change in the storage device, or methods and even a change in the operating system will not affect the internal model. 7. Using the above diagram, write all of the business rules.

The business rules are that: 

Each REGION can have many stores (1: M) and each STORE can be only in one REGION (1: 1).



Each STORE can have many EMPLOYEES (1: M) and each EMPLOYEE can belong only to one STORE (1: 1).



Each EMPLOYEE can have only one JOB (1:1) and each JOB can have many EMPLOYEES (1: M).

2

8. What is the difference between a database and a table?

A database is a structure that contains one or multiple tables and metadata. A table contains a logical structure of intersecting rows (entities) and columns (attributes) that represent an entity set in the relational model.

9. Using the STUDENT and PROFESSOR tables in the figure above, illustrate a natural between the STUDENT and PROFESSOR tables.

Natural first step: PRODUCT STU_CODE

PROFESSOR PROF_CODE

ESTUDENT PROF_CODE DEPT_CODE

100278 100278 100278 100278

1 2 3 4

2 6 6 4

128569

2

1

2

128569

2

2

6

128569

2

3

6

128569

2

4

4

3

512272 512272 512272 512272 531235 531235 531235 531235 531268 531268 531268 531268 533427 533427 533427 533427

4 4 4 4 2 2 2 2

1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4

1 1 1 1

2 6 6 4 2 6 6 4 2 6 6 4 2 6 6 4

Natural second step: SELECT STU_CODE

PROFESSOR PROF_CODE

128569 512272 531235 553427

ESTUDENT PROF_CODE DEPT_CODE 2 4 2 1

2 4 2 1

Natural second step: PROJECT

STU_CODE PROF_CODE DEPT_CODE 128569 2 6 512272 4 4 531235 2 6 553427 1 2

4

6 4 6 2

10. Using the STUDENT and PROFESSOR tables in the figure above, illustrate a right outer between the STUDENT and PROFESSOR tables. STU_CODE PROF_CODE DEPT_CODE 100278 531268 553427 128569 531235 512272

1 2 2 4

2 6 6 4

11. What does it mean to say that a database displays both entity integrity and referential integrity? Entity integrity is the condition in which each row (entity instance) goes; the table has its own unique identity. To ensure entity integrity, the primary key has two requirements: (1) all of the values in the primary key must be unique and (2) no key attribute in the primary key can contain a null. The Referential integrity, the condition in which every reference to an entity instance by another entity instance is valid. In other words, each foreign key entry must either be null or a valid value in the primary key of the related table. The requirements on values that are not null make it impossible to have an invalid value. 12. What are the requirements that two relations must satisfy in order to be considered union-compatible? The tables must have the same attribute characteristics; in other words, the columns and domains must be compatible. When two or more tables share the same numbers of columns, and their corresponding columns share the same or compatible domains.

5

13. Using the above diagram, identify the business rules depicted. The business rules are that:    

A professor can teach many classes. Each class is just taught by one professor. A professor can advise many students Each student can be advised just by one professor

14. Explain a logical model. A logical model describes data as much possible, this include entities, attributes relationships, constraints, primary keys for each entity and foreign keys. Basically determine if the requirements of the business rules are completed. Logical design is used to translate the conceptual design into the internal model independent of the actual software that will be used. 15. Explain a physical model. It is model in which physical characteristics such as location, path and format are described for the data. describing the data requires the definition of both the physical storage devices and the (physical) access methods required to reach the data within those storage devices, making it both software and hardware dependent

6

16. Use the tables shown in the figure above; create the table that results from applying a UNION relational operator to the tables.

BOOTH_PROD UCT chips Cola Energy Drink Chips Chocolate Bar

BOOTH_PRICE 1.5 1.25 2 1.25 1

17. Use the tables shown in the figure above; create the table that results from the MACHINE table DIFFERENCE the BOOTH table. MACHINE_PRODUCT Chips Chocolate Bar

7

MACHINE_PRICE 1.25 1

18. Using the figure above, identify the primary key and the foreign key(s). If a table does not have a foreign key, write none in the space provided.

TABLE

PRIMARY KEY

FOREIGN KEY(S)

EMPLOYEE

EMP_CODE

JOB_CODE

BENEFIT

EMP_CODE + PLAN_CODE

EMP_CODE, PLAN_CODE

JOB

JOB-CODE

None

PLAN

PLAN_CODE

None

19. Using the figure above, identify if the tables exhibit entity integrity. Answer yes or no, and explain your answer.

TABLE

ENTITY INTEGRITY EMPLOYEE Yes

EXPLANATION

BENEFIT

Each relation of EMP_CODE and PLAN_CODE values is unique and it does not contain null values.

Yes

Each value in EMP_CODE is unique and it does not contain null values.

8

JOB

Yes

PLAN

Yes

Each value in JOB_CODE is unique and there are no nulls. Each value in PLAN_CODE is unique and it does not contain null values.

20. Using the figure above, identify if the tables exhibit referential integrity. Answer yes or no, and explain your answer. Write NA (Not Applicable) if the table does not have a foreign key.

TABLE

REFERENTIAL INTEGRITY Yes

EXPLANATION

BENEFIT

Yes

Each JOB_CODE value in JOB it’s related in the EMPLOYEE´s table. Each EMP_CODE value in EMPLOYEE it's related in the BENEFIT´s table and each PLAN_CODE value in PLAN it's related in the BENEFIT´s table.

JOB PLAN

NA NA

---

EMPLOYEE

9