Assignment 4

In what normal form is the LOTS relation schema in the Figure with respect to the restrictive interpretations of normal form that take only the primary key into account? Candidate Key (a) LOTS County_name Lot# Area Price Tax_rate Property_id# FD1 FD2 FD3 FD4 2NF Both 2NF and 3NF 3NF 4NF

Identify if the following relation is in 1NF: STUDENT(StudentID, FirstName, LastName, (CourseID, CourseName, CourseLocation))

NORMALIZATION EXERCISE   Based on the given relation schema below, perform normalization up to 3NF. Write your answer in below space   ORDER (Order_ID,Date,Cust_ID,Cust_Name,State,City,ItemNO,ItemDescription,ItemPrice,Quantity)   1NF   2NF   3NF

A relation may not have a super key. (a) True (b)False

Write relation algebra for following:Student( Enrno, name, courseId, emailId, cellno)Course(courseId, course_nm, duration)i) Add a column city in student table.ii) Find out list of students who have enrolled in “computer” course.iii) List name of all courses with their duration.iv) List name of all students start with “a”.v) List email Id and cell no of all mechanical engineering students.

What is an unnormalized relation? Is it a relation according to the definition of the word relation?

Discuss the differences between the candidate keys and the primary key of a relation.

Convert the following table to third normal form (3NF). In this table, StudentNum determines StudentName, NumCredits, AdvisorNum, and AdvisorName. Advisor Num determines AdvisorName. CourseNum determines Description. The combination of StudentNum and CourseNum determines Grade. STUDENT (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName, CourseNum, Description, Grade). Identify the primary key for the original table and the primary keys for the resulting tables. Indicate the foreign keys with italic font. Give meaning names to the resulting tables.

What are three cases when a surrogate key might be used in place of a relation's main key?

What exactly is a Ternary Relationship, and how does it function?

Indicate dependences and their type.

Given the following relations (the primary keys are underlined): MATCH (CodM, Home Team VisitingTeam, Date,League) PLAYER (CodP, Name, Surname, Role, Nation, Team) GOAL (CodP, CodM, MinutesIntoTheMatch) YELIOW-CARDS (CodP, CodM, MinutesIntoTheMatch)   Please note that MinutesIntoTheMatch indicates the time at which either the goal was scored or the yellow card was cautioned (e.g.. between 1st and 90th).   Write SQL Query for each match in which no more than 2 players playing as defender (role= Defender) were cautioned with a yellow card, show match code date on which the match was held, and total number of goals scored by each player

Convert this table to a relation in first normal form by entering appropriate data in the table. What are the determinants in this relation?

What are three advantages of using a surrogate key rather than the main key to access a relation instead?

When should you use a surrogate key instead of the main key in a relation, and provide three examples?

1)A tuple is a named column of a relation. True or alse2)Each attribute in a relation has a distinct name. T/F3)The degree of a relation is the number of attributes it contains. T/F4)A foreign key is the candidate key that is selected to identify tuples. T/F

Create an unnormalized relation using student and class entities.

Convert the following ERD to relation schemas. EXPLAIN your answer. Dept Course Id Name Name number Offer by Depts Courses Teach in Teach Instructors PID Name

Any relation must have at least ONE Select one: a. Foreign Key b. Composite key  c. Alternate key d. Candidate key

which rules apply to desigining table with less redundancy? a) Binary Relationship b) Reflective Relationship c) Weak entity d) Normal form

Convert all relations to third normal form, if necessary, and draw a revised relational schema.

Explain the use of the terms relation, tuple, and attribute.

Explanation of three cases when it makes sense to create a secondary key instead of the main one in a relation.

What are the conditions under which an attribute of a relation may have a changeable length?

Write the Relation Model, using SQL, for the gig Relationship Set shown in this ER Model: The Band and Venue Entity Sets both have the conflicting name as their Primary Keys. Only the gig relation needs to be defined for this question.