IS320_Week 2 Exercise_IngramJ

docx

School

Grantham University *

*We aren’t endorsed by this school

Course

320

Subject

Computer Science

Date

Jan 9, 2024

Type

docx

Pages

8

Uploaded by Ingram254

Report
Running head: [SHORTENED TITLE UP TO 50 CHARACTERS] 1 Week 2 Exercise Jason Ingram Grantham University
[SHORTENED TITLE UP TO 50 CHARACTERS] 2 3.8: Show an alternative design for the attribute described below, that uses only entity types (including weak entity types, if needed) and relationship types. { PreviousEducation ( CollegeName, StartDate, EndDate, { Degree (DegreeName, Month, Year) }, { Transcript (CourseName, Semester, Year, Grade) } ) } An alternative design of the example above while keeping track of the original University is as follows: The weak entity (a). Attendance (b). Transcript The strong entity (a) Binary M:N relationship Degree_Awarded between Attendance and Degree (b) Binary 1:N relationship Attended between College and Attendance. (c) Binary 1:N relationship Previously_Attended_College among Student and Attendance. (d) Binary 1:N relationship Maintain_Attendance among Attendance and Transcript. 3.9: Consider the ER diagram in Figure 3.21, which shows a simplified schema for an airline reservation system. Extract from the ER diagram the requirements and constraints that produced this schema. Try to be as precise as possible in your requirements and constraints specification. Airports have several different plans that can land and take off: (a) Each fare has one plane. (b) Each LEG_INSTANCE is assigned to one plane.
[SHORTENED TITLE UP TO 50 CHARACTERS] 3 (c) Each LEG_INSTANCE is assigned to a reservation. Every reservation is assigned to a seat. (d) Each flight has several legs. (e) Each plane belongs to airplane type.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
[SHORTENED TITLE UP TO 50 CHARACTERS] 4 5.11: Suppose that each of the following Update operations is applied directly to the database state shown in Figure 5.6. Discuss all integrity constraints violated by each operation, if any, and the different ways of enforcing these constraints. Insert <'Robert', 'F', 'Scott', '943775543', '1972-06-21', '2365 Newcastle Rd, Bellaire, TX', M, 58000, '888665555', 1> into EMPLOYEE. No constraint violations. Insert <'ProductA', 4, 'Bellaire', 2> into PROJECT. There is a violation because DNUM=2, while also having no tuple for the DEPARTMENT relation with DNUMBER=2. In order to enforce this constraint, a rejection of the insertion of the new PROJECT tuple is necessary, along with changing the value of DNUM for a new PEOJECT tuple to an existing DNUMBER value in the DEPARTMENT, and inserting a new DEPARTMENT tuple with DNUMBER=2. Insert <'Production', 4, '943775543', '2007-10-01'> into DEPARTMENT. This is a violation of both key constraints and referential integrity. This violates the key constraints because of the DEPARTMENT tuple and DNUMBER=4. In order for this to work, rejecting the insertion, while also changing the value of DNUMBER for the new DEPARTMENT tuple, a new value will be needed that does not violate the key constraint. MGRSSN=943775543 is a violation for referential integrity because there is no tuple in the EMPLOYEE relation. For this to work, you would need to enforce the constraint by changing the value of the MGRSSN value in EMPLOYEE and insert a new EMPLYOEE type with the SSN=943775543 Insert <'677678989', NULL, '40.0'> into WORKS_ON.
[SHORTENED TITLE UP TO 50 CHARACTERS] 5 This violates three things, referential, integrity, and entity integrity. This violates the entity integrity because the PNO is the primary key of WORKS_ON. To correct this, you will need to enforce this constraint by eliminating the insertion, changing the value of the PNO for the new WORKS_ON tuple, the value would need to be PNUMBER that already exists in the PROJECT relation. This also violates the referential integrity because of the ESSN=677678989, this is because there is no EMPLOYEE tuple with this relation. To correct this by again rejecting the insertion, change the value of ESSN to an existing SSN value in EMPLOYEE and insert a new EMPLOYEE tuple with the SSN=677678989. Insert <'453453453', 'John', 'M', '1990-12-12', 'spouse'> into DEPENDENT. This violates no constraints. Delete the WORKS_ON tuples with Essn = '333445555'. This violates no constraints. Delete the EMPLOYEE tuple with Ssn = '987654321'. This violates referential integrity. This is a violation because there are several tuples that already exist in the WORKS_ON, DEPENDENT, DEPARTMENT, and EMPLOYEE relations that reference these tuples being deleted. The correction that are needed is to reject the deletion, delete all the tuples and values that is equal to 987654321. Delete the PROJECT tuple with Pname = 'ProductX'. This line violates the referential integrity because there are two tuples that exist in WORKS_ON that reference the tuple being deleted. To correct this, you will need to reject the deletion of the tuples with the values of PNO=1. Modify the Mgr_ssn and Mgr_start_date of the DEPARTMENT tuple with Dnumber = 5 to '123456789' and '2007-10-01', respectively. This violates no constraints.
[SHORTENED TITLE UP TO 50 CHARACTERS] 6 Modify the Super_ssn attribute of the EMPLOYEE tuple with Ssn = '999887777' to '943775543'. This violates referential integrity. There is a new value for the SUPERSSN=943775543 and there is no tuple with this SUPERSSN. You will need to reject the deletion of the constraint, and insert a new EMPLOYEE with the SSN=943775543 Modify the Hours attribute of the WORKS_ON tuple with Essn = '999887777' and Pno = 10 to '5.0'. This violates no constraints. 5.13: Consider the relation CLASS(Course#, Univ_Section#, Instructor_name, Semester, Building_code, Room#, Time_period, Weekdays, Credit_hours). This represents classes taught in a university, with unique Univ_section#s. Identify what you think should be various candidate keys, and write in your own words the conditions or assumptions under which each candidate key would be valid. (a) {Univ_Section#} would need to be different throughout the semester. (b) {Instructor_Name, Semester} would only work if one course if taught by an instructor each semester. (c) {Semester, Building_Code, Room#, Time_Period, Weekdays, Credit_Hours} Has a specific time each semester and can not be done in the same room. (d) {Course#, Semester, Univ_Section#} can be candidate key if Univ_Section# is not different. (e) { Semester, Univ_Section#} if Univ_Section# is different that all parts are given different numbers.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
[SHORTENED TITLE UP TO 50 CHARACTERS] 7 5.14: Consider the following six relations for an order-processing database application in a company: CUSTOMER(Cust # , Cname, City) ORDER(Order# , Odate, Cust#, Ord_amt) ORDER_ITEM(Order# , Item# , Qty) ITEM(Item# , Unit_price) SHIPMENT(Order# , Warehouse# , Ship_date) WAREHOUSE(Warehouse# , City) Here, Ord_amt refers to total dollar amount of an order; Odate is the date the order was placed; and Ship_date is the date an order (or part of an order) is shipped from the warehouse. Assume that an order can be shipped from several warehouses. Specify the foreign keys for this schema, stating any assumptions you make. What other constraints can you think of for this database? Foreign key: 1) Order# of SHIPMENT- order takers can only take these orders. 2) Cust# of ORDER- familiar customers. 3) Order# of ORDER_ITEM- Foreign key od ORDER. 4) Warehouse# of SHIPMENT- this is done from business warehouse. 5) Item# of ORDER_ITEM- items that are in stock are only taken. Additional constraints 1. Ord_amt needs to be greater than Unit_price. 2. Ship_date must be greater than Odate in order.
[SHORTENED TITLE UP TO 50 CHARACTERS] 8

Related Documents

Week5_SAFMEDS Assignment copy.pdf
Week 8 SAFMEDS Assignment copy.docx
IT-FP2280 Assessment 2.docx
IT FP2280- Assessment 6.docx
Murphy_Lab5_WorkInsideLab.docx
Murphy_Assignment3.docx
IS320_Week 1 Exercise_IngramJ.docx
IS320_Week 2 Assignment_IngramJ.docx
Week2Assgn2_Adams_D.docx
probablity of getting a straight in poker.png
4.4 Guided Practice.camkin1822 (1).docx
Post Lab 6.docx
Recommended textbooks for you
Text book image
Systems Architecture
Computer Science
ISBN:9781305080195
Author:Stephen D. Burd
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
C++ for Engineers and Scientists
Computer Science
ISBN:9781133187844
Author:Bronson, Gary J.
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
C++ Programming: From Problem Analysis to Program...
Computer Science
ISBN:9781337102087
Author:D. S. Malik
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Systems Architecture
Computer Science
ISBN:9781305080195
Author:Stephen D. Burd
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Text book image
C++ for Engineers and Scientists
Computer Science
ISBN:9781133187844
Author:Bronson, Gary J.
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
C++ Programming: From Problem Analysis to Program...
Computer Science
ISBN:9781337102087
Author:D. S. Malik
Publisher:Cengage Learning
SEE MORE TEXTBOOKS