Question

ChangeRequest(CRID, CRType, CRTitle, CROriginDate, CRPriority, CRNeedEvent, CRStatus)
NeedByEvent(Event)
CRPrevState(CRID, CRState, StartDate, EndDate)
CRAssigned(CRID, EmpID, StartDate, EndDate)
Employees(EmpID, FirstName, LastName, JobTitle)

ChangeRequest(CRID, CRType, CRTitle, CROriginDate, CRPriority, CRNeedEvent, CRStatus)

The CRID is the primary key, it is unique, and it is an positive integer
The CRType may be one of two values: "Deficiency" or "Enhancement"
CRTitle is a variable length string that may be up to 2048 characters
CROriginDate is a date
CRPriority is an integer that may assume a value of "1", "2", "3", "4", or "5".
CRNeedEvent and CRStatus are a variable length string of no more than 48 characters. The CRStatus refers to the current status.

Only the CRPriority and the CRNeedEvent may be null.

1) Provide the SQL to list the IDs and Titles of all deficiencies (not enhancements) with a CRNeedEvent of "TestEvent2".

2) Provide the SQL to establish this relation accounting for all this information. Be sure to define types.

3) Provide the SQL that ensures that only a "DatabaseAdministrator" may delete a record from the ChangeRequest relation.

4) Provide the SQL to list the IDs and Titles of all change requests with an unknown priority.

Assume that all "need by" events referenced by "CRNeedEvent" are maintained in a separate table with the following schema.

NeedByEvent(Event)

5) Provide the SQL for a "check" whereby on any update or addition of a tuple, the CRNeedEvent must already exist in the NeedByEvent table.

6) Given that a tuple in the ChangeRequest relation has variable length strings, what is the physical organization/implementation of a ChangeRequest record.

7) Provide an example of a ChangeRequest using JSON notation. Assume that the CRNeedEvent is "TestEvent1", the type is "Deficiency", and the CRStatus is "Open". Choose other data as you see fit.

8) Using the same data as the previous problem, provide the XML representation.

9) Using JDBC and prepared statements, provide source code to change the CRStatus of the change request with ID = 1000 to "Assigned".

Assume that a record of previous states is maintained for each change request. The current status is maintained in the ChangeRequest relation.

CRPrevState(CRID, CRState, StartDate, EndDate)

All values are not null.

Also, assume that a change request may be assigned to one or more employees. The schema appears as follows.

CRAssigned(CRID, EmpID, StartDate, EndDate)

The 'start date' and 'end date' define when they are were assigned. A currently assigned employee will have an end date of null. The end date is also always set when the CR is closed. Employees are not assigned to "Closed" CRs.

Employee information is found in the following relation.

Employees(EmpID, FirstName, LastName, JobTitle)

10) Provide the SQL to list the ID and titles of all change requests that had previously been in a "Closed" state but are currently not in a "Closed" state.

11) Provide the SQL to list the IDs and Titles of all change requests that are currently assigned to an employee with the job title of "Project Lead".

12) For the date "July 1, 2020", provide the SQL to list the ID, the CR title, and all assigned employees (first name and last name) for all change requests with a priority of "1" or "2".  

13) Provide the SQL to list the IDs, Titles, and the total number of all assigned employees (past or current) for change requests with a current status of "Closed".

14) Provide the SQL to list the IDs, Titles, and the total number of days for all assigned employees of change requests with a status of "Closed" where the total number of days of employees assigned to the change request is thirty (30) or greater and list the change requests in the order of the total number of days (highest first).

15) Provide an E-R diagram that correctly accounts for the CRAssigned, Employees, ChangeRequest, CRPrevState, and NeedByEvent relations and their relationships.

16) Provide an argument for the whole set of relation schema as to whether or not the CRAssigned, Employees, ChangeRequest, CRPrevState, and NeedByEvent relations are either in 3NF or BCNF. Justify your argument. Hint: Consider definitions.

17) Assume that for deficiencies, there is an associated "Cause" which is variable string. Assume that for an enhancement, there is associated "Benefit" which is also a variable string. Provide the portion of an E-R diagram that depicts deficiencies and enhancements as specific "types" of change requests.

18) Provide the SQL:1999 to define a 'ChangeRequest' type. Also provide the SQL to use this 'ChangeRequest' type to define a 'Deficiency' type as defined in the previous question.

Assume that change requests may be created or separated from other change requests. This information is stored in a relation with the following schema where a "parent" CR may have 0 or more "child" CRs but a "child" CR can have only one "parent" CR.

DerivedCRs(ParentCRID, ChildCRID, CRRationale)

CRRationale is a variable string that describes the reason why the child change request (CR) is created.

19) Show the fragment of an E-R diagram that depicts the DerivedCRs and the ChangeRequest relations.

20) Using the DerivedCR relation described above, provide an SQL query to show all the CRs (parents and their parents) that led to the CR with ID #2525. Note: This uses recursion.

21) Describe how to use functional dependencies to show that certain decompositions are lossless.

22) List and describe the four "ACID" properties of a transaction.

23) Describe a "man in the middle" attack and describe how such an attack may be thwarted.

24) The six basic operators of relational algebra are select, project, cartesian product, union, set difference, and rename. What is the equivalent language for these operators in SQL?

25) Explain the concept of "physical data independence" and describe why it is important to database management systems.

26) For a B+ tree, assume that 999 key values fit into a node. Assume that an associated relation has 80000 entries and that ten (10) entries fit into a disk block. What is the minimum and maximum number of disk block accesses needed to access a record (given the appropriate primary key)? Show all work.

27) Assume a relation is structured with a sequential file organization and has fixed length records. Also assume that after processing, the relation is fully "sequentially ordered" and that no subsequent changes have been made. Assume a disk block has 10240 bytes, a record is 1000 bytes, and an index is 16 bytes. The indices are "sparse" and at the bottom level, point to the first record of the pertinent disk block. How many disk blocks are needed to account for the records and indices? Show all work and note any assumptions.

28) Assume that a magnetic disk has an average seek time of 10 msec., has a rotational speed of 6000 revolutions per minute, and a data transfer rate of 1 MByte per second. What is the average time needed to retrieve 2048 contiguous bytes?

29) Describe what a "materialized view" is and also describe how it might help and hinder performance.

30) Describe how to avoid an SQL injection attack.

31) Describe the five responsibilities of a database management system (DBMS).

32) Describe the difference between a candidate key, a foreign key, a superkey, and a primary key.

33) Explain how public and private keys are used when a receiver needs to verify the identity of a message's sender.

34) For a query, provide an example of a capability or feature in SQL that is not "possible" in relational algebra.

35) Using Armstrong's Axioms, prove that the decomposition rule is sound.

A relation schema R with attributes { A, B, C, D, E, F, G, H, I } and the following functional dependencies F is used for problems 36-40

A -> BC C->DE DF->GHI AC->EG BH->EG   

36) What is the attribute closure of A? Show all work.

37) Provide a candidate key for R. Prove that it is a candidate key. Show all work.

38) Provide a canonical cover for the functional dependencies. Show all work.

39) Provide a relational schema in BCNF. Show all work.

40) Determine whether or not the relational schema from problem #39 should instead have been constructed in 3NF. Justify this determination.

Answer 1

SHORT SUMMARY:

• As per HOMEWORKLIB POLICY, experts are allowed to answer only four sub parts per question. Please post remaining questions as separate post.
• Provided the solution for first four questions.
• Please upvote the answer and appreciate our time.

SOLUTION:

2) Provide the SQL to establish this relation accounting for all this information. Be sure to define types.

• Table creation is done using by defining Data types.

Defining Data Types:

-- CRID Data type

CREATE TYPE [dbo].[CRID] FROM INT NOT NULL

GO

CREATE RULE RuleCRID

AS

@CRID > 0

GO

EXEC SP_bindrule 'RuleCRID', 'CRID'

GO

--CRType

CREATE TYPE [dbo].[CRType] FROM [varchar](20) NOT NULL

GO

CREATE RULE RuleCRType

AS

@CRType in ('Deficiency','Enhancement')

GO

EXEC SP_bindrule 'RuleCRType', 'CRType'

GO

--CRTitle

CREATE TYPE [dbo].[CRTitle] FROM [varchar](2048) NOT NULL

GO

--CROriginDate

CREATE TYPE [dbo].CROriginDate FROM [date] NOT NULL

GO

--CRPriority

CREATE TYPE [dbo].[CRPriority] FROM [int]

GO

CREATE RULE RuleCRPriority

AS

@CRPriority BETWEEN 1 AND 5

GO

EXEC SP_bindrule 'RuleCRPriority', 'CRPriority'

GO

-- CRNeedEvent

CREATE TYPE [dbo].[CRNeedEvent] FROM [varchar](48)

GO

-- CRStatus

CREATE TYPE [dbo].[CRStatus] FROM [varchar](48) NOT NULL

GO

Table Creation:

CREATE TABLE ChangeRequest(

CRID CRID PRIMARY KEY,

CRType CRType,

CRTitle CRTitle,

CROriginDate CROriginDate,

CRPriority CRPriority,

CRNeedEvent CRNeedEvent,

CRStatus CRStatus);

Insertion to tables:

INSERT INTO ChangeRequest VALUES(123,'Deficiency','Test title','20-Mar-2020',1,'TestEvent2','Active');

INSERT INTO ChangeRequest VALUES(234,'Enhancement','Test title1','15-Feb-2020',2,'TestEvent1','Notactive');

INSERT INTO ChangeRequest VALUES(567,'Enhancement','Test title2','26-Dec-2019',NULL,'TestEvent2','Suspended');

INSERT INTO ChangeRequest VALUES(789,'Deficiency','Test title3','18-Apr-2020',3,'TestEvent3','Dismissed');

INSERT INTO ChangeRequest VALUES(891,'Deficiency','Test title4','29-May-2020',5,'TestEvent4','Active');

INSERT INTO ChangeRequest VALUES(1245,'Deficiency','Test title2','02-Mar-2020',NULL,'TestEvent2','Active');

SELECT * FROM ChangeRequest;

1 1 2 3 CRID CRType CRTitle CROrigin Date CRPriority CRNeedEvent CRStatus 123 Deficiency Test title 2020-03-20 TestEvent2 Active 234 Enhancement Test title1 2020-02-15 2 TestEvent1 Notactive 567 Enhancement Test title2 2019-12-26 NULL TestEvent2 Suspended 789 Deficiency Test title3 2020-04-18 3 TestEvent3 Dismissed 891 Deficiency Test title4 2020-05-29 5 TestEvents Active 1245 Deficiency Test title2 2020-03-02 NULL TestEvent2 Active 4 5 6

1) Provide the SQL to list the IDs and Titles of all deficiencies (not enhancements) with a CRNeedEvent of "TestEvent2".

Query:

SELECT CRID, CRTitle FROM ChangeRequest WHERE CRType = 'Deficiency' AND CRNeedEvent = 'TestEvent2';

Results e Messages CRID CRTitle 123 Test title 1245 Test title2 1 2

3) Provide the SQL that ensures that only a "DatabaseAdministrator" may delete a record from the ChangeRequest relation.

Query:

CREATE ROLE DatabaseAdministrator;

GRANT DELETE TO DatabaseAdministrator;

4) Provide the SQL to list the IDs and Titles of all change requests with an unknown priority.

Query:

SELECT CRID, CRTitle FROM ChangeRequest WHERE CRPriority IS NULL

Results gi Messages CRID CRTitle 567 Test title2 1245 Test title2 1 2

**************************************************************************************

Feel free to rate the answer and comment your questions, if you have any.

Please upvote the answer and appreciate our time.

Happy Studying!!!

**************************************************************************************