Follow the normalization process to normalize the above tables (you need not show the steps, but can use the blank sides of the exam if you want to work out the steps) and show the 3NF relational schema for each resulting table in the following format: ?NF TABLENAME (PRIMARY KEY, other attributes, FOREIGN KEY(s)) Use appropriate names for all tables and attributes. Do not draw any dependency diagrams. 2. Use the above relational schemas to draw the Crow’s Foot E-R Diagram (include all attributes, PKs, and FKs).

Follow the normalization process to normalize the above tables (you need not show the steps, but can use the blank sides of the exam if you want to work out the steps) and show the 3NF relational schema for each resulting table in the following format: ?NF TABLENAME (PRIMARY KEY, other attributes, FOREIGN KEY(s)) Use appropriate names for all tables and attributes. Do not draw any dependency diagrams. 2. Use the above relational schemas to draw the Crow’s Foot E-R Diagram (include all attributes, PKs, and FKs).

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter6: Additional Database Objects

Section: Chapter Questions

Problem 6HOA: A new table has been requested to support tracking automated emails sent to customers. Create the...

See similar textbooks

Similar questions

A new table has been requested to support tracking automated emails sent to customers. Create the table and add data as described below. Tablename: email_log Columns: emailid (numeric), emaildate (datetime), customer# (numeric) Primary key: emailid column, define as an Identity Column Add the following data rows and display resulting rows (if any errors occur, explain why the error is expected) Emaildate = current date, customer# = 1007 Emailid = specify to use the column default value, emaildate = current date, customer# = 1008 Emailid = 25, emaildate = current date, customer# = 1009
Create LessonSchedule table with FK constraints Two tables are created: Horse with columns: ID - integer, primary key RegisteredName - variable-length string Student with columns: ID - integer, primary key FirstName - variable-length string LastName - variable-length string Create the LessonSchedule table with columns: HorseID - integer with range 0 to 65 thousand, not NULL, partial primary key, foreign key references Horse(ID) StudentID - integer with range 0 to 65 thousand, foreign key references Student(ID) LessonDateTime - date/time, not NULL, partial primary key If a row is deleted from Horse, the rows with the same horse ID should be deleted from LessonSchedule automatically. If a row is deleted from Student, the same student IDs should be set to NULL in LessonSchedule automatically. Note: Table and column names are case sensitive in the auto-grader.
8. Create a trigger for the Invoices table that automatically inserts the vendor name and address for a paid invoice into a table named ShippingLabels. The trigger should fire any time the PaymentTotal column of the Invoices table is updated. The structure of the ShippingLabels table is as follows: CREATE TABLE ShippingLabels (VendorName varchar(50), VendorAddress1 varchar(50), VendorAddress2 varchar(50), VendorCity VendorState VendorZipCode varchar(50), char(2), varchar(20)); Use this UPDATE statement to test the trigger: UPDATE Invoices SET PaymentTotal = 67.92, PaymentDate = '2020-02-23' WHERE InvoiceID = 100;
Topic: Database Software: SQLite Schema Table: CREATE TABLE name_basics (nconst VARCHAR(45) NOT NULL, primaryName VARCHAR(45) NULL, birthYear YEAR(4) NULL, deathYear YEAR(4) NULL, age INT NULL, movieNum INT NULL, PRIMARY KEY (nconst)); CREATE TABLE attributes (attributeID INT NOT NULL,attribute VARCHAR(45) NOT NULL,PRIMARY KEY (attributeID)); CREATE TABLE attributes_collection (akasID INT NOT NULL,attributeID INT NOT NULL,PRIMARY KEY (akasID, attributeID),CONSTRAINT attributeIDFOREIGN KEY (attributeID)REFERENCES attributes (attributeID)ON DELETE NO ACTIONON UPDATE NO ACTION);CREATE TABLE crew_directors (crewID INT NOT NULL,nconst VARCHAR(45) NOT NULL,PRIMARY KEY (crewID, nconst),CONSTRAINT crewIDFOREIGN KEY (crewID)REFERENCES title_crew (crewID)ON DELETE NO ACTIONON UPDATE NO ACTION,CONSTRAINT nconstFOREIGN KEY (nconst)REFERENCES name_basics (nconst)ON DELETE NO ACTIONON UPDATE NO ACTION);CREATE TABLE crew_writers (crewID INT NOT NULL,nconst VARCHAR(45) NOT NULL,PRIMARY KEY…
Write Create Table SQL statements Work Session table The table must have a primary key. The table must have appropriate foreign key constraints. Foreign keys must have the same name, datatype and size of the primary key that they refer to The following columns datatypes and sizes and named constraints must be used Column DataType Constraint WorkYear number(4) Range 2019 - 2020 WorkWeek number(2) Range 1-52 WorkHours number(4,2) Range 0.5 – 99.99 BID NUMBER(4) ,AUTHID NUMBER(4)
This is data base question. Schema is also uploaded. 1. Show all the movie names with their type names for those movies which has been released during 2000 to 2009. (Use inner join) 2. Show the customer names along with the movie names for which they booked more than 5 seats. (Use inner join) 3. Show the Name of Staff members and their designation who did some booking for the movie ‘Avengers’. (Use inner join) 4. Show the name of movies which were released in both 2D and 3D. (Use Natural join) 5. Show names of all movies along with the staff names who did booking for them. Show all movie names whether they have been booked by any staff or not. (Use Outer Join) 6. Show all category names along with the movie names that lie in the category. Show all categories whether any movie is there of that category or not. (Use Outer Join) 7. Show all customer names along with movie names they booked. Show all the customer names whether they booked any move or not. Also show all movie names whether…
Topic: Database Software: SQLite Schema Table: CREATE TABLE name_basics (nconst VARCHAR(45) NOT NULL, primaryName VARCHAR(45) NULL, birthYear YEAR(4) NULL, deathYear YEAR(4) NULL, age INT NULL, movieNum INT NULL, PRIMARY KEY (nconst)); CREATE TABLE title_episode (episodeID VARCHAR(45) NOT NULL,parentTconst VARCHAR(45) NULL, seasonNumber INT NULL,episodeNumber INT NULL, PRIMARY KEY (episodeID), CONSTRAINT parentTconst FOREIGN KEY (parentTconst) REFERENCES title_basics (tconst) ON DELETE NO ACTION ON UPDATE NO ACTION); CREATE TABLE title_principals (tconst VARCHAR(45) NOT NULL, ordering INT NULL, nconst VARCHAR(45) NULL, category VARCHAR(45) NULL,job VARCHAR(45) NULL, characters VARCHAR(45) NOT NULL, PRIMARY KEY (tconst, characters), CONSTRAINT nconst FOREIGN KEY (nconst)REFERENCES name_basics (nconst) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT tconst FOREIGN KEY (tconst) REFERENCES title_basics (tconst) ON DELETE NO ACTION ON UPDATE NO ACTION); CREATE TABLE title_ratings…
Create table EMP and DEPT with the below mentioned structureStructure for EMP tableEmpIDNUMBER(4)DeptIDVARCHAR2(10)EmpNameCHAR(10)JobCHAR(10)HireDate DATESalary NUMBER(7, 2)Commission NUMBER (7, 2)Structure for DEPT tableDeptId VARCHAR2(10)Deptname VARCHAR2(20)No_of_Faculties NUMBER(2)In table EMP : EmpID should be the Primary Key and DeptID should be the foreign key.In table DEPT : DeptId should be the primary key. a) Find out the number of employees having “manager” as job.b) Display only the jobs with maximum salary greater than or equal to 3000c) Find all those employees whose job does not start with ‘M’. Find the names of the employees who are Managers and their date of joining is after “02-Jan-2006”.
SQL: Add a table-level PRIMARY KEY constraint to the EMPLOYEE table using the ID column. The constraint should be named at creation. (Which means you'll need to drop the table, then recreate.) Create a PRIMARY KEY constraint on the DEPARTMENT table using the ID column. The constraint should be named at creation. (Which means you'll need to drop the table, then recreate.) Add a foreign key reference on the EMPLOYEE table that will ensure that the employee is not assigned to a nonexistent department. Confirm that the constraints were added by querying USER_CONSTRAINTS. Note the types and names of the constraints and include them in your statement comments. Display the object names and types from the USER_OBJECTS data dictionary view for EMPLOYEE and DEPARTMENT tables. You will want to format the columns for readability. Provide comments that include the new tables and indexes created. Modify the EMPLOYEE table by adding a SALARY column of NUMBER data type, precision 7. Confirm…
The view V_PAT_ADT_LOCATION_HX returns one row of information per bed stay. The column ADT_DEPARTMENT_ID is the ID of the bed's department. The column ADT_DEPARTMENT_NAME is that department's name. Two distinct departments can potentially share the same name. Which of the following queries would return exactly one row per department and display the name of the department? A. SELECT ADT_DEPARTMENT_ID, MIN(ADT_DEPARTMENT_NAME) FROM V_PAT_ADT_LOCATION_HX GROUP BY ADT_DEPARTMENT_ID B. SELECT MIN(ADT_DEPARTMENT_ID), ADT_DEPARTMENT_NAME FROM V_PAT_ADT_LOCATION_HX GROUP BY ADT_DEPARTMENT_ID C. SELECT 'ADT_DEPARTMENT_ID', ADT_DEPARTMENT_NAME FROM V_PAT_ADT_LOCATION_HX GROUP BY ADT_DEPARTMENT_ID D. SELECT ADT_DEPARTMENT_ID, 'ADT_DEPARTMENT_NAME' FROM V_PAT_ADT_LOCATION_HX GROUP BY ADT_DEPARTMENT_ID
CREATE TABLE MEMBERSHIP ( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEM_NUM_PK PRIMARY KEY, MEM_FNAME VARCHAR(30) NOT NULL, MEM_LNAME VARCHAR(30) NOT NULL, MEM_STREET VARCHAR(30), MEM_CITY VARCHAR(10), MEM_STATE CHAR(2), MEM_ZIP CHAR(5), MEM_BALANCE NUMBER (2)); CREATE TABLE RENTAL ( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY, RENT_DATE DATE, MEM_NUM CHAR(3), CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP); CREATE TABLE PRICE (PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICE_CODE_PK PRIMARY KEY, PRICE_DESC VARCHAR(20), PRICE_RENTFEE NUMBER (3,1), PRICE_DAILYATFEE NUMBER(3,1)); CREATE TABLE MOVIE (MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIE_NUM_PK PRIMARY KEY, MOVIE_NAME VARCHAR(30) NOT NULL, MOVIE_YEAR CHAR(4), MOVIE_COST NUMBER(5,2), MOVIE_GENRE VARCHAR(15), PRICE_CODE CHAR(1), CONSTRAINT MOVIE_PRICE_CODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES PRICE); CREATE TABLE VIDEO (VID_NUM CHAR(5) CONSTRAINT VIDEO_VIDEO_NUM_PK PRIMARY KEY, VID_INDATE…
Q#1 Insert two records in each table or use if you have already created this schema.Q#2 Display Employees Fname and salary who are males and salary is greater than equal to1000.Q#3 Display Address of employees where salary range starting from 1000 to 2000.Q#4 Display Last name of employee that starts with A and end on B.Q#5 Display Fname of employee that starts with A and have size of 3. Like: Ali,Ans etcQ#6 Display Project name and Project number that belongs to department 1.Q#7 Display Dependent name who were not born in 2002,2003 and 2004.Q#8 Display Pno who take a time more than 3 hours and less than 10 hours.Q#9 update working hours 3 hours to 5 hours.Q#10 Delete record in Dependent who have gender Male.