Module 4 Case: Relational Algebra And SQL DML Statements
Module 4 - Case
RELATIONAL ALGEBRA AND SQL DML STATEMENTS
Assignment Overview
Developing a Database System: Populate the Tables
In the Case Assignment of this module, you will write SQL statements to query and modify the PC Store database. You will insert initial data into the database, update the database when necessary, and query the database to produce various reports required by the store owners.
Case Assignment
Use SQL command to:
Create related tables to capture sales data and sales transactions.
Populate all tables with the related data.
Update the database when new products arrive.
Update the database when a sale is made.
Describe (in at least a half page) the major points you learned in the readings (especially on SQL) and what lessons you have learned in this exercise.
Assignment Expectations
Successfully use SQL for data manipulation, mainly insertion, and update.
Communicate effectively with your audience.
Algebra and DML Statements
Name
Institutional Affiliation
Algebra and DML Statements
Task 1: Table to capture the sales data and sales transactions.
The table used to capture sales data and sales transactions is the sales order table.
The SQL statements used to create the table are;
Create table sales_order_table (
Sales_order_number char not null primary key,
Zip char not null,
Zip_code char not null,
Customer_number char not null,
Social_security_number char not null,
Sale_amount char not null,
Date date not null,
Comment text,
Foreign key(zip) references employee_table(zip),
Foreign key(zip_code) references customer_table(zip),
Foreign key(customer_number) references customer_table(customer_number),
Foreign key(social_security_number) references employee_table(social_security_number)
);
Task 2: Populate all tables
Employee table;
INSERT INTO `employee_table` (`social_security_number`, `zip`, `first_name`, `second_name`, `birthdate`, `status`, `salary`, `date_of_hire`) VALUES ('14', '201', 'Princess', 'Santa', '1981-01-09', 'hired', '9.3', '2013-3-11');
Employee address table
INSERT INTO `employee_address_table` (`Zip`, `Address`, `City`, `State`) VALUES (93, '78 California', 'California', 'Orange');
INSERT INTO `employee_address_table` (`Zip`, `Address`, `City`, `State`) VALUES (90, '76 Avenue Long Island', 'Texas', 'Small City');
Customer table;
INSERT INTO `customer_table` (`customer_number`, `zip`, `first_name`, `second_name`, `email`, `telephone`, `customer_since`) VALUES ('c02', '4568', 'John', 'Kins', '[email protected]', '542-212-892', '2014-08-04');
Customer address table
INSERT INTO `customer_address_table` (`Zip`, `Address`, `City`, `State`) VALUES ('4568', 'Private Address Park Avenue', 'Washington DC', 'Washington');
Sales order table
(Note, the sales order table, sales order number was altered to auto increment to improve on the insert operation)
INSERT INTO `sales_order_table` (`Sales_order_number`, `Zip`, `Zip_code`, `Customer_number`, `Social_security_number`, `Sale_amount`, `Date`, `Comment`) VALUES (NULL, '90', '4568', 'c02', '13', '100', '2016-12-25', NULL);
Task 4: updating the database when subsequent sales are made
The database is updated via a transaction in the sales order table. The transaction captures the sales order number, and respective details of the customer and...
π Other Visitors are Viewing These APA Research Paper Samples:
- Module 4 SLP Project Planning, Scheduling, Budgeting, Control and Maintenance4 pages/β1100 words | 6 Sources | APA | IT & Computer Science | Research Paper |
- ITM490 Information Technology Management Module 1 Case3 pages/β825 words | 7 Sources | APA | IT & Computer Science | Research Paper |
- ITM490 Capstone in Information Technology Module 1 - SLP2 pages/β550 words | 9 Sources | APA | IT & Computer Science | Research Paper |
- Enterprise Resource Planning Systems Research 5 pages/β1375 words | 10 Sources | APA | IT & Computer Science | Research Paper |
- Module 4 - Case Management Integration And Implementation3 pages/β825 words | 8 Sources | APA | IT & Computer Science | Research Paper |
- Module 4 - SLP Management Integration And Implementation3 pages/β825 words | 9 Sources | APA | IT & Computer Science | Research Paper |
- HIM615 v10R InformationTechnologies Assignment Sheet 1 page/β275 words | 2 Sources | APA | IT & Computer Science | Research Paper |