Your Task In This Assignment Is To Set Up A Connection To Ac

Your Task In This Assignment Is To Set Up A Connection To Acadoradbprd

Your task in this assignment is to set up a connection to acadoradbprd01.dpu.depaul.edu with SQLDeveloper and write an SQL script to create and populate a small database consisting of four linked relations. Steps: 0. First, verify run SQLDeveloper and create a new connection to acadoradbprd01.dpu.depaul.edu (follow the instructions in CDMOracleTutorial.pdf included with Lecture1 materials). I strongly recommend that you try to establish a connection to acadoradbprd01.dpu.depaul.edu as soon as possible so that you have time to work out any problems that arise with your account and password. Next, write a script to do the following: 1. Create a relational database schema consisting of the four relation schemas given in the diagram below, representing various entities recorded by a furniture company. Define all necessary attributes, domains, and primary and foreign keys. You should assume the following: Each CustomerID is a number with at most three digits, each OrderID is a number with at most five digits, and each ProductID is a number with at most two digits. Each Name is at most 26 characters long, and each Address is at most 50 characters long. Each Quantity is a positive whole number between 1 and 100, inclusive. Each Description is at most 29 characters long, and each Finish is at most 12 characters long. Each Price is a non-negative value (zero is allowed) representing a price in dollars and cents that cannot exceed 999.99. To avoid conflicts, include DROP TABLE commands for all four tables before your CREATE TABLE statements. Either drop tables containing foreign keys before the tables containing the referenced primary keys, or use CASCADE CONSTRAINTS. Verify that the tables have been defined correctly before proceeding. 2. Populate the database as follows: CUSTOMER: CUSTOMERID, NAME, ADDRESS; FULLORDER: ORDERID, ORDERDATE, CUSTOMERID; PRODUCT: PRODUCTID, DESCRIPTION, FINISH, PRICE; REQUEST: ORDERID, PRODUCTID, QUANTITY. Ensure that you insert records containing primary key values before inserting records with foreign keys that reference them to prevent insertion failures. 3. Display the contents of each table by adding four SELECT FROM TABLE_NAME; statements at the end of your script. 4. Include a comment at the top of the script with your name, course number and section, assignment number, and submission date, e.g.: / YourName CSC 453 Section YourSection Assignment 1 SubmissionDate */. 5. Run the complete script and verify that the tables display the correct contents. Remarks: 1. Do not use SQLDeveloper's tool-generated scripts to create or insert rows; write your own CREATE and INSERT statements manually and include them in your script. 2. You may use a different front-end application or a different DBMS (e.g., SQL Server), but specify which you used at the top; only the SQL script will be graded. 3. Always verify that uploaded files are correct and readable by downloading and opening them immediately after submission. 4. All work must be completed individually. Ensure your SQL script fully meets all assignment requirements.

Paper For Above instruction

This assignment involves establishing a database connection to a specified database server, creating a relational database schema for a furniture company, populating it with sample data, and verifying the data through SELECT queries. The process begins with configuring an SQLDeveloper connection to acadoradbprd01.dpu.depaul.edu, ensuring proper access credentials are in place. This initial step is crucial to facilitate subsequent script execution and data handling.

The core task involves designing and implementing four interrelated tables: CUSTOMER, FULLORDER, PRODUCT, and REQUEST. Each table is constructed with carefully defined attributes, adhering to specified data constraints such as maximum lengths, numeric ranges, and data types, to maintain data integrity and consistency. The primary keys uniquely identify each record, while foreign keys establish relationships between tables.

To avoid referential integrity issues during table creation, the script starts with DROP TABLE statements, removing existing tables if any, with a preference for dropping tables containing foreign keys after the referenced tables or using CASCADE CONSTRAINTS. The CREATE TABLE commands follow, establishing the schema with all necessary constraints. Examples include defining CUSTOMERID as a NUMBER (3 digits), with NAME as a VARCHAR2(26), and similar constraints for other attributes based on the provided specifications.

Subsequently, the assignment requires populating these tables with a predefined set of data. The insertion order is critical; primary key records are inserted first to avoid foreign key violations, followed by dependent data. Sample data respects the constraints on lengths, ranges, and formats. For example, CUSTOMER: CUSTOMERID=2, NAME='CASUAL FURNITURE', ADDRESS='PLANO, TX'; FULLORDER: ORDERID, ORDERDATE, CUSTOMERID; PRODUCT: PRODUCTID=10, DESCRIPTION='WRITING DESK', FINISH='OAK', PRICE=300; REQUEST: ORDERID, PRODUCTID, QUANTITY, with values that align with the stated rules.

Finally, the script concludes with four SELECT * FROM table statements to display all records from each table. This allows verification that the data was correctly inserted and the schema functions as intended.

It is important to include a comment at the top of your script containing your personal and assignment information. The entire script should be executed in one go, and the output should display the populated tables with the correct data. Care must be taken to avoid common mistakes such as foreign key violations or incorrect data formats.

References

  • Oracle Corporation. (2021). SQL Developer User Guide. Oracle Documentation.
  • Elmasri, R., & Navathe, S. B. (2015). Principles of Database Systems (7th ed.). Pearson.

Related Assignments