Skip to main content

Posts

Showing posts from October, 2020

SQL Set Operators

SQL Set Operators   Traditional Set Operators: Union Compatibility:     Requirement for the traditional set operators.    Strong requirement.                         - Same number of columns.                         - Each corresponding column is compatible.                         - Positional correspondence.     Apply to similar tables by removing columns first. SQL UNION Example:           Example 1: Retrieve basic data about all university people.                     SELECT FacNo AS PerNo, FacFirstName                                      ...
  d. Insert a new row in the Location table related to the Facility row in modification problem (c). The new row should have “Door” for the location name. Query Formulation + Result Snapshot Our Official Website :  Web Conquerors  (https://www.webconquerors.com/)  Want to get digital services?  Contact US Want to know about our services?  Our Services
 c. Insert a new row into the Facility table with facility name “Swimming Pool”.  Query Formulation + Result Snapshot Our Official Website :  Web Conquerors  (https://www.webconquerors.com/)  Want to get digital services?  Contact US Want to know about our services?  Our Services
 b. List the plan number, line number, resource name, number of resources ( eventplanline.number ), location name, time start, and time end where the event is held at the basketball arena, the event plan has activity of activity of “Operation”, and the event plan has a work date in the period October 1 to December 31, 2018. Your query must not use the facility number (“F101”) of the basketball arena in the WHERE clause. Instead, you should use a condition on the FacName column for the value of “Basketball arena”. Query Formulation + Result Snapshot Our Official Website :  Web Conquerors  (https://www.webconquerors.com/)  Want to get digital services?  Contact US Want to know about our services?  Our Services

Database Queries

 You will write SELECT statements that retrieve some information from the following populated tables. This assignment uses intercollegiate athletic database and builds on what you have already created during your course work. Event Plan Table EventPlan Line      Event Request Table Resource Table Facility Table                                                                       facno                Facname                                                                       F100       ...

Identify the following Errors in Queries:

  a. Identify errors in the following SQL statement and label errors with error type. To simplify your work, the statement has only one type of error. Rewrite the statement to remove the error. SELECT eventrequest.eventno, dateheld, status, estcost FROM eventrequest, employee, facility, eventplan WHERE estaudience > 5000 AND eventplan.empno = employee.empno AND eventrequest.facno = facility.facno AND facname = 'Football stadium' AND empname = 'Mary Manager'; CORRECTION : SELECT DISTINCT eventrequest.eventno, dateheld, status, estcost FROM eventrequest, employee, facility, eventplan WHERE estaudience > 5000 AND eventplan.empno = employee.empno AND eventrequest.facno = facility.facno AND facname = 'Football stadium' AND empname = 'Mary Manager'; ERROR :  Missing join between event-plan and event-request tables. It results in incorrect number of rows . ERROR TYPE: Semantic Error. b. Identify errors in the following SQL statement and label errors with...

Correction For ERD

 d. For each consistency error in Figure 1, you should identify the consistency rule violated and suggest possible resolutions of the error. The ERD has generic names to help will concentrate on finding diagram errors rather than focusing on the meaning of the diagram. Answer:                Entity6 is violation of the Primary key rule. As there are 3 attributes from within Entity6 that are marked as primary key. Entity6 also violates the Identifying relationship rule, as there is no foreign key in the Entity6 table to identify the item from Entity7. Since this a weak entity type, the cardinality must be 1:1, which it is not. Entity2 is a violation of the Identifying relationship rule due to it not being a weak entity type when there is an identifying relationship. Our Official Website :  Web Conquerors  (https://www.webconquerors.com/)  Want to get digital services?  Contact US Want to know about our services?  ...

You will draw an ERD diagram on ERAssistant to demonstrate certain requirements for problem (a). Then, you will extend the initial ERD for further requirements in problems (b) and (c). In problem (d), you will identify consistency errors in a given ERD diagram and redraw the ERD without the errors.

 a. Create an ERD containing the TestOrder and Specimen entity types and a 1-M relationship from Specimen to TestOrder. For each specimen collected, the database should record a unique SpecNo, SpecArea (ectocervix, cervical, or endocervical), and SpecCollMethod (thin prep or sure path). A test order contains a TONo (primary key), TOTestName, TOTestType (HPV, CT/GC, CT, or GC), and TOTestResult (positive, negative, equivocal, or failure). A specimen does not have a test order until a delay, from hours to days. If a test order produces a failure, the specimen is given a new test order and tested again until a non-failure result is obtained. A test order is created for exactly one spe cimen. Answer: Snapshot of ERD: b. Augment your ERD from problem (a) with the Supply entity type and an M-N relationship between TestOrder and Supply. Choose a name for the relationship based on your common knowledge of test orders and supplies. A test order can use a collection of supplies (0 or more) a...