Module Three Assignment - CS340

Task 1: The InstantRide Management team founded a new team for car maintenance. The new team is responsible for the small maintenance operations for the cars in the InstantRide system. The main idea is to take actions faster and minimize the time spent for the maintenance. Therefore, the Car Maintenance team wants to store MAINTENACE_TYPE_ID (char(5)) and a MAINTENANCE_TYPE_DESCRIPTION (varchar(30)) in the database. Using MAINTENANCE_TYPE_ID as the PRIMARY KEY, create a new table, MAINTENANCE_TYPES, and send the table description with the column names and types to the Car Maintenance team. Task 2: The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENACNE_TYPE_ID should be foreign…

Task 1: The InstantRide Management team founded a new team for car maintenance. The new team is responsible for the small maintenance operations for the cars in the InstantRide system. The main idea is to take actions faster and minimize the time spent for the maintenance. Therefore, the Car Maintenance team wants to store MAINTENACE_TYPE_ID (char(5)) and a MAINTENANCE_TYPE_DESCRIPTION (varchar(30)) in the database. Using MAINTENANCE_TYPE_ID as the PRIMARY KEY, create a new table, MAINTENANCE_TYPES, and send the table description with the column names and types to the Car Maintenance team.

1. You want to design a database for a restaurant. Customers have em password. Each customer can have several credit cards registered in the system, each credit card has a 16 digit number, an expiration date, a 3 digit number (CVC code), and the name on the card. The menu items have a short description, a long descriptioin and a price. Customers can place orders that have the date/time that they were placed, one or several menu items (for each menu item it also keeps the quantity), and the total price (summation of the prices of the items mutiplied by the quatity of each item). There are two types of orders: dine-in, or pick-up. In the case of pick-up orders the system must have the car make, model, and color of the person picking up the order, as well as a parking spot number. Dine-in orders have a tip. In addition, the system needs to know if an order has been paid or not. Draw a complete ER diagram. Your diagram must include ISA relationships. If you need to make additional…

struct student { char name[20]; char studentID[10]; char phonenum [9]; char advisor [20]; float gpa; } Operations needed: addnewStudent { purpose: to create a new student in the database input: name, studentID.phonenum,advisor.gpa output: none } Name another operation besides add that we could use for our student data structure.

Need a c++ program to connect to this database and perform add, search, remove, display operations. -- students  create table students   (    student_id int,             name varchar(50),    dob date,    phone int,    email varchar(50),    address text,    year_level int,    section varchar(2),    primary key(student_id)    ); -- staff  create table staff(staff_id int,name varchar(50),address text,phone int, email varchar(50),position varchar(20),salary int,primary key(staff_id)           );   -- book_subject  create table(    subject_id int primary key,    subject varchar(50)); -- books create table books( book_id int,title varchar(50),published_year int,isbn int,cost int, subject_id int, primary key(book_id),foreign key(subject_id) references book_subject(subject_id) );   -- book_issue create table book_issue(    issue_id int,    issue_date date,    staff_id intjumb,    student_id int,    book_id int,    return_date da    primary key(issue_id),    foreign key(staff_id) references…

What are the main distinctions between a secondary index and a clustering index?

what is index

How is a clustered index created as opposed to a sparse one, and what characteristics make it different from a sparse index?

WRITE THE CODE OF MYSQL IN PYTHON  Create database and tables as given below by using Python. (a)  Market database (b)  Vendor: vendor_id integer, vendor_name varchar, license_start date, license_end date (c)  Item: item_id integer, item_name varchar, item_type varchar, previous_price float, last_sold_date date (d)  Auction: auction_id integer, vendor_id integer, item_id integer, price float Choose primary and foreign keys depending on the relations in Market database design.

How is a clustered index constructed, and what features set it apart from a sparse index?

name PERSON address salary fee EMPLOYEE STUDENT thesis RESEARCH TEACHING POST GRAD UNDER GRAD Year = 4 FINAL YEAR LECTURER TUTOR SUPERVISOR project courseno PART II: University Database in MYSQL • Create a database and its tables and constraints based on the relational model of PART I. • You can use my slides and any other resources to complete this exercise. • Submit the SQL statements you used to create the database and its tables (you can copy/paste directly from the mysql console).

1. Create a PL/SQL function. The minimal requirements: using the cursor in PLSQL function, retrieve data from object table and record the data in the PL/SQL table, make calculations with object data and define return value of the function, test the operation of the function (get the result). The maximal requirements: you can add IF-ELSEIF's, cursor processing in loops, more interesting semantic functions. 2. Create methods for object types. The minimal requirements: use map/order methods (1 method) (method+ demonstration), use MEMBER methods (2 methods) (methods + demonstration). The maximal requirements: non-trivial methods, that use more complex comparisons or do more complex calculations – i.e., give a couple more constructs in code. 3. Demonstrate the multiset operators. The minimal requirements: using 2 different multiset operators (can be included in the code), they can also be used in the The maximal requirements: the queries make sense semantically, one of the queries is a…

What are the key features that set a secondary index different from a clustering index?

CSC 472 Introduction to Database Systems Project Consider the contents of output tuples generated by a join operator. The advantage of "late materialization" approach is that future operators in the query plan never need to go back to the base tables to get more data. Group of answer choices True False

Explain step by step

There are three tables in this database (see image). Write MYSQL code for: (1) Create a trigger “insert_inventory” on table “Inventory”. The trigger is fired after a row is inserted in table “Inventory”. After a row is inserted in table “inventory”, the “itemid”, the insertion time, and the action is inserted in table “Inventory_history”. The action is set to ‘add an item’. The oldprice is set to null.  Test your trigger by inserting a row into Inventory and displaying the contents of Inventory_history. (2) Create a trigger “change_quantity” on table “Transaction”. The trigger is fired after a row is inserted in table “Transaction”. After a row is inserted in table “Transaction”, update the “quantity” in table “Inventory”. For example, if 3 iWatch are sold, then the quantity of iWatch in table “Inventory” is decreased by 3.  Test your trigger by inserting a row into Transaction and displaying the contents of the relevant row in Inventory. (3) Create a trigger “change_price” on table…

Store the following fields for a library database: AuthorCode, AuthorName, BookTitle, BorrowerAddress, BorrowerName, BorrowerCardNumber, CopiesOfBook, ISBN (International Standard Book Number), LoanDate, PublisherCode, PublisherName, and PublisherAddress. A one-to-many relationship exists between publishers and books. Many-to-many relationships exist between authors and books and between borrowers and books.a. Name the entities for the library database b. Without creating a set of dependency diagrams, write the relational schema that meet 3rd NF requirements. c. Draw the Crow’s Food entity-relationship diagram for the library database.

Consider the following database instance. Table name: Students  Primary key: sid Sid sname 7 Ricky 2 Ellen 6 MaryLou 4 Ellen   Table name: Courses  Primary key: cid cid cname 1 ICS 2 Finance   Table name: Register Primary key: sid,cid Foreign key: sid references Students(sid) Foreign key: cid references Courses(cid) sid cid 7 2 2 2 7 1 4 1   5.1) For each of the following statements, show whether the statement is correctly executed or not (assume that the statements are executed in order, which means that if a statement is correctly executed, its effect is reflected in the following statement). If you say the statement is not executed, explain why. INSERT INTO Students VALUES (3, ‘Ellen’);     INSERT INTO Students VALUES (6, ‘Ellen’);     INSERT INTO Register VALUES (1, 2);     INSERT INTO Courses VALUES (5, ‘Systems’);     INSERT INTO Register VALUES (6, 5);     INSERT INTO Register…

Consider the following database instance. Table name: Students  Primary key: sid Sid sname 7 Ricky 2 Ellen 6 MaryLou 4 Ellen   Table name: Courses  Primary key: cid cid cname 1 ICS 2 Finance   Table name: Register Primary key: sid,cid Foreign key: sid references Students(sid) Foreign key: cid references Courses(cid) sid cid 7 2 2 2 7 1 4 1   5.1) For each of the following statements, show whether the statement is correctly executed or not (assume that the statements are executed in order, which means that if a statement is correctly executed, its effect is reflected in the following statement). If you say the statement is not executed, explain why. INSERT INTO Students VALUES (3, ‘Ellen’);     INSERT INTO Students VALUES (6, ‘Ellen’);     INSERT INTO Register VALUES (1, 2);     INSERT INTO Courses VALUES (5, ‘Systems’);     INSERT INTO Register VALUES (6, 5);     INSERT INTO Register…

The following relations kind a part of a on-line database (the relations area unit self-explanatory):   CUSTOMER/CLIENT/SHOPPERS/BUYERS(Cust#, CAddress, Ctown, Balance,Cname) ORDER(Order.#, Date, Cust#.) ORDERLINE(Order#, Part#., Amount-Ordered) PART(Part#., Desc, Stock, Item-Class, Unit -Price)   Design a physical ER for the above scenario.   With respect to this relative schema, write SQL statements for the subsequent queries:   List the name of the client United Nations agency has placed Order 123 (i.e. Order: = 123). List the names of shoppers United Nations agency in Cardiff and have ordered half 456 (i.e. Part. 456). List the order numbers (Order) for orders that contain a minimum of three elements and also the quantity ordered are minimum of ten. List the outline (Desc), of these elements that the typical quantity ordered is bigger than the number ordered for half .6 (Part. = 456) so as 123.(Order. .123).

a) Choose a database application with which you are familiar. Design a schema and show a sample database for that application, using the notation of Figure 1 and Figure 2.  b) Write SQL update statements to do the following on the database schema shown in Figure 1. i. Insert a new student, <‘Johnson’, 25, 1, ‘Math’>, in the database. ii. Change the class of student ‘Smith’ to 2.

Consider the following schema for a company database and answer the following questions: DEPARTMENT Dname Dnumber Mgr_ssn Mgr start date M EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Dno M WORKS ON Essn Pno Hours M PROJECT Pname Pnumber Plocation Q1//create all tables of this schema and apply any necessary constraints Q2// Write SQL statements that carry out the following tasks: 1-alter the employee table to add column city of 20 characters. 2- alter the employee table to apply the following business rule The minimum salary is 3000 and maximum salary is 10000 3- insert sample data in each table. 4- display a list of each employee SSN , Name and Salary. 5- display a list of each employee SSN, Name and Salary for employees working in department no 2.

Write Two CREATE USER Statements for the Publisher -Author - Books - Marketing: Database -- drop the database Publisher -Author - Books - MarketingDB if it already exists   DROP DATABASE IF EXISTS PublisherAuthorBooksMarketingDB;   -- create a database with name Publisher -Author - Books - MarketingDB   CREATE DATABASE IF NOT EXISTS PublisherAuthorBooksMarketingDB;   -- ensure that the database is used   USE PublisherAuthorBooksMarketingDB; -- create the table for Publisher   DROP TABLE IF EXISTS Publisher;   CREATE TABLE IF NOT EXISTS Publisher (   pid VARCHAR(25),   pubName VARCHAR(50),   email VARCHAR(50),   address VARCHAR(50),   phone VARCHAR(15), CONSTRAINT Publisher_PK PRIMARY KEY(pid)   ); -- create the table for Author DROP TABLE IF EXISTS Author;   CREATE TABLE Author(   socSecNum VARCHAR(15),   authName VARCHAR (50),   phone VARCHAR (15),   address VARCHAR (50),   email VARCHAR (50),   CONSTRAINT Author_PK PRIMARY KEY (socSecNum)   );   -- create the table for Marketer…

What separates a clustering index from a secondary index?

The aim of assignment 1 is to implement a database schema and execute different types of SQL queries in Oracle SQL developer. Given the following relational schema: Student (studid, FirstName, LastName, Email, phoneNumber, DateofBirth, GPA) Club (clubid, ClubName, #studid) MemberOf (#clubid, #studid, joiningDate ) Activities (actid, actdt, place, durationNbHour) Organize (#actid , #clubid , fee) 1. Write SQL queries to create the following tables according to the below descriptions. Table name: Student studid |Char(9) Primary key FirstName Varchar2(50) NOT NULL LastName Varchar2(50) NOT NULL Email Varchar2(50) NOT NULL, UNIQUE phoneNumber Number(8) NOT NULL, UNIQUE DateofBirth Date NOT NULL GPA Number(1,2) NOT NULL Table name: Club clubID Number(3) Primary key ClubName Varchar2(20)NOT NULL NULL, UNIQUE, NOT Foreign key studid Char(9) Table Name: MemberOf ClubID Number(3) Primary Key, Foreign Key Studid Char(9) Primary Key, Foreign Key joiningDate Date Table Name: Activities Actid…

Why are certain functional dependencies said to be trivial?

Consider the following structure of an 'inventory Oracle database. cuelomer *** MUMERIC ($) VARCHAR 30) VARCHAR (15 DECIMAL) SALESMANJD NAME CUSTOMER JD ERC ($) CUST NAME RCHAR (30 CHAR (15 MUMERIC (3) CITY CITY COMMISSION GRADE SALESMAN ID NUMERIC ($) orders ORD NO NUMERIC (5) PURCH AMT DECIMAL (8,2) ORD DATE DATE CUSTOMER ID NUMERIC (5) SALESMAN ID NUMERIC (5) Find the highest purchase amount with customer ID of the one who made it, along with the order date; for those customers who have a higher purchase amount in a day within the range 2000 and 6000. Use the editor to format your answer 81