Question 1 a. i. What is a Primary Key? State the rules for defining primary ke: (3.5marks) ii. What is Candidate Key? Differentiate between a Candidate and Alternative Key and state the properties for each. (6 marks) iii. From the Table below, identify the primary key, Candidate Key and the Alternate key StudID Roll No First Name LastName Email 1 11 Tom Price abc@gmail.com 2 12 Nick Wright xyz@gmail.com 3 13 Dana Natan mno@yahoo.com (3 marks) b. i. When do we need to store derivable data in the table and what are the advantages and disadvantages of storing derivable data in a table? (4marks) ii. Apart from containing sequences of SQL code in a script, describe other ways that SQL code can be stored, contained, encapsulated and run as a sequence of executable statements (2.5 marks) iii. Describe four possible benefits of "Views" in databases. (2 marks) c. Define the basic characteristics of a NoSQL database, with examples of where is used (2 marks) d. Explain how the entity relationship (ER) model helped produce a more structured relational database design environment (2 marks)
Added by Abraham Y.
Close
Step 1
Row-level filtering: This technique is used to filter out rows that do not meet a certain condition. For example, if we have a table named 'Students' and we want to select only those students who have scored more than 90 in Maths, we can use the following SQL Show moreā¦
Show all steps
Your feedback will help us improve your experience
Akash M and 50 other AP CS educators are ready to help you.
Ask a new question
Labs
Want to see this concept in action?
Explore this concept interactively to see how it behaves as you change inputs.
Key Concepts
Recommended Videos
Implement a new strong entity phone in the Sakila database. Attributes and relationships are shown in the following diagram: The phone entity appears on the right. The phone entity contains four attributes, each followed by cardinality information: phone_id 1-1(1), country_code M-1(1), phone_numer M-1(1), and phone_type M-1(0). Three entities appear on the left: store, staff, and customer, connected to the phone entity by three identical relationships. The three relationships are named 'has' and have cardinality 1(0) on both sides. The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply these constraints: NOT NULL constraints correspond to cardinalities on the diagram above. Foreign key actions are SET NULL for delete rules and CASCADE for update rules. Specify a suitable column as the phone table primary key.
Supreeta N.
1. 2. 3. 4. 5.
Dominique Jan T.
Recommended Textbooks
Computer Science and Information Technology
Introduction to Programming Using Python
Computer Science - An Overview
Transcript
18,000,000+
Students on Numerade
Trusted by students at 8,000+ universities
Watch the video solution with this free unlock.
EMAIL
PASSWORD
