The practical development work is based on an online electronics shopping company where you work as a Database Analyst/Developer. The entity-relationship diagram and SQL script for creating and populating the database are provided.
Assessment Task
1. Introduction
This assessment requires you to undertake practical database application development work to meet specified requirements and write an reflective evaluation report that discusses the quality of the work completed and the approaches used.
This assessment will enable students to demonstrate in full or in part the learning outcomes identified in the module descriptor.
2. The Practical Development Work
The practical development work is based on an online electronics shopping company where you work as a Database Analyst/Developer. The entity-relationship diagram and SQL script for creating and populating the database are provided.
The entity-relationship diagram and SQL script for creating and populating the database are provided. The entities in the diagram are Customers, Orders, Order Details, and Products. The relationships between the entities are one-to-many (one customer can have many orders, one order has many order details, and one product can be in many orders) and many-to-many (one customer can have many products, one product can be in many customers). The SQL script includes the CREATE TABLE, INSERT INTO, and UPDATE statements.
The entity-relationship diagram is shown in Figure A and the SQL script is provided in Appendix B.
Figure A: Entity-Relationship Diagram for Populating a Database
Appendix B: SQL Script for Populating a Database
In this blog post, we will discuss how to make an SQL script. This is a very important skill for any database administrator or developer. An SQL script can be used to create, modify, or delete data in a database. In addition, it can be used to run queries against a database. In this tutorial, we will use the MySQL command line client to create and execute an SQL script. Let’s get started!
First, you will need to open the MySQL command line client. To do this, open the Terminal (Mac) or Command Prompt (Windows). Then, type in the following command:
mysql -u [username] -p
Replace [username] with your MySQL username. When prompted, enter your password. This will log you into the MySQL command line client.
Next, we will create a new file called script.sql. To do this, type in the following command:
touch script.sql
This will create an empty file called script.sql in the current directory. You can use any text editor to edit this file. We will be using nano:
nano script.sql
Now, let’s write some SQL code! We will start by creating a new table called employees. To do this, type in the following SQL code:
CREATE TABLE employees (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
job_title VARCHAR(255),
department VARCHAR(255)
This SQL code will create a new table called employees with four columns: id, name, job_title, and department. The id column is the primary key and it is set to auto-increment. This means that each time we insert a new row into the table, the id column will be automatically populated with a unique number.
Next, we will insert some data into the table. To do this, type in the following SQL code:
INSERT INTO employees (name, job_title, department) VALUES (‘John Doe’, ‘Developer’, ‘IT’);
INSERT INTO employees (name, job_title, department) VALUES (‘Jane Smith’, ‘Accountant’, ‘Finance’);
INSERT INTO employees (name, job_title, department) VALUES (‘Joe Johnson’, ‘Salesperson’, ‘Sales’);
This SQL code will insert three rows of data into the table. The first row is for John Doe who is a developer in the IT department.