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
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.
Assume two entities ORDER and PRODUCT and the following business rules: An ORDER can contain one...
QUESTION 1 The following tables form part of a database held in a relational DBMS for storing the information of Customer, Item, Transaction, and temSale A transaction can contain multiple items and an item can be in multiple transactions as depicted in the following sample data Item(itemid, name, category, price Itemid pk) name category price Iteml Pencil Stationery $30 paperStationery Item2 $25 $15 Item3 Cards Stationery S40 Item4 Ink Stationery S13 Item5 Sprite Itemo Food S4 Milk Food Transaction ctransid,...
PROBLEM STATEMENT: Suppose you have a client that has given you the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club’s members, to plan the meals, to keep track of who attends the dinners, and so on. Each dinner serves many members, and each member may attend many dinners. A member receives many invitations, and each invitation is mailed to many...
6. With respect to database systems, which of the following statement(s) is (are) true? a. The physical view of data is how people conceptually organize and understand the relationships among data items. b. The DML builds the data dictionary, creates the database, describes logical views for each user, and specifies security constraints. c. A record layout shows the items stored in a file, including the type of data stored and both the order and length of the data fields. d....
Start Access. Open the downloaded Access file named exploring_acap_grader_a1_Loans.accdb. Import the exploring_acap_grader_a1_Clients.xlsx Excel workbook into a table named Clients. While importing the data, make sure to select the option First Row Contains Column Headings, and select ClientID as the primary key field. After you have completed the import, open the Clients table in Design view. Change the ClientID field size to 6 and remove the @ symbol from the ClientID format property. Change the ZIP field size to 5. Change...
Overview This lab provides you the opportunity to insert and update data with the use of SQL commands. The lab will utilize the FLIX2YOU problem, the current schema. In order to start this lab, you must have successfully completed Lab # 6. In Lab # 6, you executed a script that was provided to you. This script created 7 of the FLIX2YOU tables as documented in the Entity Relationship Diagram in the FLIX2YOU problem document. The second part of lab...