Question

Assume two entities ORDER and PRODUCT and the following business rules: An ORDER can contain one or more PRODUCTs. A PROUDCT can be in zero or more ORDERs. How would you convert this model into an ERM that displays only 1:M relationships? Be specific and complete. Draw the resulting ERM. 2. What is the difference between entity integrity and referential integrity? Given an example of each. 3. Suppose you have an EMPLOYEE table which has one row per employee. The primary key of this table is the EMP_NUM. If we wanted to allow for a quicker lookup by employee last name and first name, what technique can we use and one what fields (and in what order) would that technique be applied? 4. Hudson valley has hired you to work on a revised student information system. You are discussing the STUDENT and CRN tables with your co-workers. Assume that you have one row per student with a PK of STUDENT_ID in the STUDENT table and one row per Course Reference Number (CRN) in the CRN table. Since STUDENT:CRN is many to many, you need a bridging entity between STUDENT and CRN. Your co-worker suggests a design for the bridge table where the PK is a composite primary key consisting of the STUDENT and CRN fields. Another co-worker suggests a design where a surrogate key (e.g. access autonumber or Oracle sequence) is used as the primary key. Which of these designs would you choose? What are the pro’s and con’s for each? Show the two designs using Crow’s foot

Answer 1

has OtH product orders

1.

ORDER can contain one or more products. A PROUDCT can be in zero or more orders.

Example

An order might yield two products: one delivered on Monday, Wednesday, and Friday (MWF) between 10:00 a.m. to 10:50 a.m., and one delivered on Thursday (Th) from 6:00 p.m. to 8:40 p.m. Therefore, the 1:M relationship between ORDER and Product might be described this way:

• Each Order can have many products, but each product references only one order.

• There will be only one row in the order table for any given row in the product table, but there can be many rows in the order table for any given row in the product table.

2.

Entity Integrity:
It states that in any database relation value of attribute of a primary key can't be null.
Ex:- Consider the relation "Student" Where "Stu_id" is a primary key and it must not contain any null value while other attributes may contain null value.

Referential Integrity:
Where as Referential Integrity states that if a forign key exists in a relation then either the foriegn key value must match a primary key value of some tuple in its relation or the foreign key value must be null.
The rules are:
1. A record cannot be deleted from a primary table if matching records exist in a related table.
2. You can't change a primary key value in the primary table if that record has relate records.
3.You can't enter the value in the foriegn key feild of the related table that doesn't exist in the primary key of the primary table.
4.You can enter a NULL value in the foreign key, specifying that the records are unrelated.

3.

DQL(data query language) statements are used.

Select query with the where condition as EMP_NUM is used to find the last name.

query: select last_name from employee where employee_num='<employee number for whom you want to fetch the last name>'

NOTE: For any quires please comment

I was supposed to answer only 3.