****** * Name: YourNameGoesHere * Class: CST 235 * Date: * I have not received or given help on this assignment: YourName ********** ******* DROP DATABASE IF EXISTS Lab3 Your LastName; CREATE DATABASE Lab3YourLastName; USE Lab3 Your LastName; /***** Task 1: create tables ******* CREATE TABLE /***** Task 2: uncaptured constraints put your response here ****** */ /***** Task 3: Add records ******* */ INSERT INTO ... ****** * * * * ***** a) Write SQL statements to create the corresponding relations and capture as many of the participation and cardinality constraints as possible. Make sure that the data type and size for each attribute are appropriate. b) If some constraints in the diagram cannot be captured, explain why. c) Write SQL statements to insert at least two tuples for each table ReceivingDate shippingDate payAmount IName ordDate fName password payMethod custID OID Phones N Orders Places 1 Customers N Contains M price qty iID Items qtyInStock name price

****** * Name: YourNameGoesHere * Class: CST 235 * Date: * I have not received or given help on this assignment: YourName ****** * DROP DATABASE IF EXISTS Lab3 Your LastName; CREATE DATABASE Lab3YourLastName; USE Lab3 Your LastName; /* Task 1: create tables *** CREATE TABLE /* Task 2: uncaptured constraints put your response here **** / / Task 3: Add records *** */ INSERT INTO ... ****** * * * * ***** a) Write SQL statements to create the corresponding relations and capture as many of the participation and cardinality constraints as possible. Make sure that the data type and size for each attribute are appropriate. b) If some constraints in the diagram cannot be captured, explain why. c) Write SQL statements to insert at least two tuples for each table ReceivingDate shippingDate payAmount IName ordDate fName password payMethod custID OID Phones N Orders Places 1 Customers N Contains M price qty iID Items qtyInStock name price

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Consider the following ER diagram: storcid address description value Displays Store Memorabilia The above diagram represents information pertaining to stores and the memorabilia that is displayed within the stores. a) Write the SQL statements to create the necessary tables to represent the above diagram, representing as many constraints as possible. Note: you do not need to worry about specifying types for the fields, but you do need to provide suitable foreign key option(s).
Write a Structured Query Language (SQL) statement for each of the following questions:a) Create the AppointmentDetails table with the following requirements:  Appropriate data types. All keys can store between 6 to 10 characters. All the columns must have values (cannot be empty). Add on charges is set to 0.00 by default. Enforce entity and referential integrities. b) Diplay the therapists’ particulars (include TheraName, TheraContact) as first two columns, then calculate the Total_Count_Of_Appointment for each therapist within the year 2020 as third columns. Lastly, arrange the outcome to show the list by the higher popularity of therapists first.
https://sqliteonline.com/ : Program link The first requirement: Using the SQL language, transform the following entity model into tables in the database. Entity Manger contains: M-name The name of the manager. M-ID and manager number.The department entity contains: D-name The name of the department. D-ID and department number.Create constraint master key name of the manager when creating the Manger table.Creating the foreign key constraint The name of the manager when creating the Department table. The second requirement: After creating the tables and specifying the primary and foreign key, do the following: * Add a column D_loc to the Department table, specifying the type of column.* Remove the column named M_name from the Manger table* Change the name of the department table to Dep Please:: ******* ATTACH A PICTURE OF THE CODES USED AND PICTURE OF THE PROGRAM SCREEN *******
(d) Write an SQL statement, using the inner join construct, that will, for the lecturer John Smith, list in alphabetical order the students taking any module which he teaches. (e) What information does the output from the following SQL query provide? SELECT Lecturer, COUNT(DISTINCT Student) FROM class INNER JOIN staff ON class.Module = staff.Module WHERE Grade < 40 GROUP BY Lecturer; (f) Write an SQL query which would obtain the following information: for each lecturer, what is the average grade obtained by students across all of the modules which they have taught?
Indicate dependences and their type.
Write CREATE TABLE statements to implement the following design. Include foreign key constraints. Define the individualID, and GroupID as identity columns. Decide which columns should allow null values, if any. Define Dues column with a default of zero and check constraints to allow only positive values.
Question 13 Given the following relation: Book (BookID, Author, ISBN, Title) Write a PL/SQL block of code that performs the following tasks: i. Read a Book id provided by user at run time and store it in a variable. ii. Fetch the Author and title of the book having the entered id and store them inside two variables iii. Display the fetched book author and title.
Write SQL code for the following design schema of three tables. The tables should have the following properties: sales table has a primary key on SalesNumber and it is set to AUTO-INCREMENT.products table has a primary key on number and it is set to AUTO-INCREMENT, they is an unique index on prodidsalesdetails table has a primary key on number and it is set to AUTO-INCREMENT, they is an index on prodid. They is a one-to-many relationship between the salesdetails.SalesNumber andsales.SalesNumber fields. There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields. There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table. Primary and foreign keys should be implemented in the appropriate tables. Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all…
Write SQL queries DDL Queries to implement the following Relational Model i.e. create the Nurse and Care_Center relations (including primary key and foreign key). Intelligently write data types.
Computer Science Create a SQL Code for the following ER diagram/Schema. choose any data type you think is appropriate for each of the attributes (int, numeric, varchar, etc.). Make sure that your SQL code includes all the constrains depicted in the schema! User Follows Follows u id follower id u id username u id birthyear User username birthyear Event Participates Participates u id e id e id topic day e id month day Event month topic (a) (b)
4. Simplify the following SQL query. SELECT EMP# FROM EMP WHERE EMP.TITLE ="PROFESSOR" OR (NOT(EMP.FNAME = "Joe") AND (EMP.FNAME = "Joe" EMP.SALARY= 3000) AND OR NOT(EMP.SALARY= 3000))
In relational model terminology, a table is considered as Select one: a. Tuple b. Range c. Relation d. Domain