Databases | SQL | Oracle | MS Access Answers

Question 1

A sequence is a database/schema object used to generate a unique auto-number. The auto-number generated through a sequence is independent of tables, which means that the same number can be used for multiple tables. 

Consider the following table: 

Assignment_No  Course_Code   Total_Marks 

1  CS201  15 

2  CS614  20 

1  CS614  10 

5  CS610  25 

3  CS409  15 

 You are required to write a PL/SQL PROCEDURE named Add that insert a new record in the above table

Question 1   

A sequence is a database/schema object used to generate a unique auto-number. The auto-number generated through a sequence is independent of tables, which means that the same number can be used for multiple tables. 

Create a Sequence named Supplier_Seq which generate an auto-number for the Supplier_ID column of the following table. 

Supplier_ID  Supplier_Name  Contact_No  Country_Name 

Note: The sequence should start with the number 202, each sequence number must be even, for faster access, the CACHE value should be 20 and there should be no cycle in the sequence. 

Get the number of videos released in each year.

Note:

• For this question, convert theyearin string datatype to INT datatype.
• Sort the output in the ascending order ofyear

Project Description

Consider an online video-sharing platform like YouTube which hosts tens of thousands of channels and crores of users.

You have to analyse the data and provide meaningful insights on the type of content that drives engagement, users growth, and many more to all the stakeholders. Let’s roll our sleeves up for an insightful analysis!

Database

The sample database consists of tables that store the information of users, channels, videos, genres and likes/dislikes.

Note:

channel_user table

channel_iduser_idsubscribed_datetime10012020-12-10 10:30:4510072020-10-10 11:30:45.........

channel_usertable stores the data of the channel_ids and their subscribers' user_ids.First row in the table represents that the user with user_id = 1 is subscribed to the channel with channel_id = 100 at

2020-12-10 10:30:45user_likes table

user_idvideo_idreaction_typereacted_at110LIKE2020-12-10 10:30:45710DISLIKE2020-10-10 11:30:45............

Read information from following table carefully and check whether the following schedule contains a deadlock or not by constructing wait for graph?       

A schedule S= {T₁, T₂, T₃, T₄, T₅, T₆, T₇, T₈} is shown with following information.

Transactions Holding Locks

Transaction waiting for Locks

Data item on which lock is required

T_2------T₁---A

T_8----T_3------B

T_3-----T_4------C

T_4----T_7------D

T_5----T_2-----X

T_6----T_8----Y

T_3----T_6----Z

Calculate Affinity Matrix on the base of given database, usage and access frequency information.

Database Information

Item

ID

Name

Price

Type

Expiry

Usage Information:

q1=select id,name,price from item

q2=select name,type,expiry from item

q3=select id,type from item where price>400

q4=select expiry,type,price from item where type=’soap’

q5=select name,type from item where expiry>15

q6=select name,expiry,id from item

Access Frequency Information

Site 1: q1(150), q2(000), q3(120), q4(300), q5(030), q6(500)

Site 2: q1(010), q2(230), q3(050), q4(250), q5(030), q6(140)

Site 3: q1(150), q2(130), q3(200), q4(110), q5(010), q6(220)

Site 4: q1(130), q2(700), q3(010), q4(230), q5(210), q6(100)

Write SQL queries for the given statements. Schema given below

Projects (CNIC, Name, Start_Date, End_Date, Type)

-         Write a trigger which does not allow user to insert data in Projects table unless following constraints are met

a.      CNIC must contain 13 digits

b.     Name must contain only alphabets

c.      Start_Date must be lesser than End_Date

d.     Duration of Project must not be more than 5 months

e.      Type can be either ‘Dev’, ‘Construction’, ‘Management’ or ‘Labor’.

f.       Not more than 3 projects of same type must be inserted.

Given is the schema of a table and fragments created. Write down which of the correctness rules of fragmentation are violated (also write reason) for each part and also rewrite new fragments (at-least 3) for each part such that all correctness rules must be satisfied.

1-     Employee (Eid, EName, eAge, eDesignation, eSalary)[ Salary must be more than 14000]

F1: eSalary > 14000 and eSalary < 20000

F2: eSalary >= 20000 and eSalary <=50000

F3: eSalary >=50000 and eSalary >= 100000

2-     Book(Book_ISBN, bTitle, bPrice, bCategory) [bCategory must not be ‘CS’ or ‘IT’]

F1: bCategory = ‘Science’

F2: bCategory = ‘Novels’

F3: bCategory != ‘Science’

F4: bCategory != ‘CS’ or bCategory != ‘IT’

Given is the details of transaction’s start time and end time. Draw a table which shows what recovery operation will be taken by DBMS at each failure point for each transaction.

i-                  Deferred Updated

Transaction Name Start Time End Time

Ta t₂ t₉

Tb t₁ t₁₁

Tc t₉ t₁₈

Td t₈ t₁₅

Te t₁₄ t₁₉

Failure Points: t7, t10, t13, t20

Check Points: t6, t12, t17

Given are relational algebraic expressions. Perform given tasks for each.

·        Write SQL statement for given queries.

·        Convert Cartesian Product into Joins and vice versa.(convert only given queries in relational algebraic form)

i-                  (((s_TSAL>=50000(Tutor))⟕_TIDAssign)⋈_C.cid=A.cid(s_{Name=’DBS’} (Course))

ii-                s_{TS.REGNO=R.REGNO^R.CCODE=C.CCODE}           (((s_CGPA>3.0(STUDENT)) x (REG)) x (s_{CATEGORY='MATHS'}(COURSE)))