In this assignment, you will design a relational database for a company that sells and installs decorative concrete. After your design is completed and correct, you will create database tables and populate them with data. You will then produce queries such as the following: Which customers have a pool and more than two acres of land? Which jobs are booked for a particular month and business partner? What type of concrete does the company use most? What is the most lucrative product? Which customers have made payments to the company? Finally, you will design a website for Decorative Concrete with at least four pages:
- A home page
- A page for staff
- Two pages for customers
The staff and customer pages will be dynamic and will show particular queries.
Two of your friends have started a business to sell and install decorative concrete. Carl, whose father owns a concrete business has extensive experience in pouring and laying concrete driveways and foundations. Candy, an art major, has done extensive decorative tiling and mosaics for friends. The couple decided to form a company called C&C Concrete. Decorative concrete is a popular alternative to tiling and plain concrete. Patterns can be stamped into wet concrete to make it look like expensive brick, and colours and alternative textures can be added to create further effects. Customers use decorative concrete for pool areas, paths, countertops, and flooring.
C&C Concrete has grown rapidly, and Candy and Carl cannot keep up with their scheduled jobs with their current paper-based system. They know you have experience in database and website design and implementation, so they have asked for your help. Customer billing and bill collection are handled by a third party, so the database does not have to account for them.
Your job is to design a database for jobs and scheduling, and then to implement the database. You will also design and implement a web site with pages for customers and staff.
Before you begin, however, you need to know a few details about the business. Candy and Carl value their customers and keep detailed records about them. Candy and Carl like to get to know each customer personally, which enables more targeted marketing. For example, new customers fill out a web page that records their name and address, number of acres on their property, whether they have a pool, and garage size (one, two, or three cars).
C&C Concrete sells three types of decorative concrete, as shown below:
Table 1: Types of Concrete
|Type||Description||Price per Square Foot|
|Basic||Basic stamped one colour||£10|
|Upgraded||Stamped with two to three colours and border||£16|
|Superior||Upgraded with hand-applied designs||£20|
The basic type of decorative concrete resembles brick; this coloured concrete is poured and stamped. The upgraded concrete product features additional pattern colouring and costs more per square foot. The superior product features embellishments and hand applications.
When Candy and Carl get a new job with a customer, they record necessary details such as the type of decorative concrete ordered, the amount of square footage covered, and the address where the work will be performed. This address is not necessarily the customer’s home address: jobs could be completed at a holiday home or work address. They are sometimes asked back to do further jobs for the same customer. To get to know customers better, Candy and Carl like to record as many additional comments as possible. In addition, the owners want to know how many jobs each type of concrete generates. They also want to know which product is the most lucrative for the company.
The two owners are very busy and they don’t employ extra help, so they need to schedule and track projects carefully to avoid overbooking. Therefore, your database should be able to record the schedule of each customer job and indicate which of the two owners will do the job. Keep in mind that all jobs take longer than one day to complete, so you need to be able to record both start and end dates.
Finally, although billing and payments are handled by a third-party company, Candy and Carl would like to know what each customer has ordered. You will create a query that summarizes information about customer job costs.
In designing your database, you should follow the first four stages of the Database Life Cycle as shown below.
- At the DBMS software selection stage you will choose MySQL as this is the DBMS currently used by the university
- You will not need to install the DBMS as it is already installed
- In the Physical Design you need only identify indexes and type of indexes to be used for each table; this is a very small database likely to be used only by one person so further physical design activities are not required
- Stages after the Testing and evaluation stage are not required
- When you have created your tables in MySQL. Use the following guidelines to insert appropriate data records:
- Create at least 10 customers and at least 15 concrete installation jobs with a range of dates
- Use the types of concrete and their descriptions and prices listed in Table 1
As part of the Testing phase, you will create a number queries as outlined below:
Create a query to display fields for the Last Name, First Name, Address, City, and State of all customers who have a pool and more than two acres of property.
Create a query to specify a type of concrete and count the number of jobs for that type. Your query should prompt for the type of concrete so that the owners can run the query for different types.
Create a query that lists each type of concrete and then shows the total square footage and total cost for all jobs that use each type of concrete. (Hint: The Total Cost field is a calculated field with aggregated data.) Sort your output to show the most profitable product first.
Create a query that lists the jobs scheduled in August for Candy. The query should display fields for the Job ID, Work Address, and Start Date for each job in a particular month.
Create a query that displays the first and last name of customers, the type of concrete they selected for their work, the square footage of the jobs, and the cost of the jobs (a calculated field). Group the query appropriately, and create a subtotal for each customer’s total cost and a grand total for all customers at the bottom of the query. Format your output appropriately, such as including currency signs. (Hint: you will need GROUP BY WITH ROLLUP)
Create a query that lists each type of concrete and shows its cost.
Create a query to show orders for a particular customer which have not yet been completed. Your query should prompt for the customer name so that the query can run for different customers.
You should design an attractive website for C&C Concrete, as follows:
- The home page should be a welcoming landing page for customers and should include
- a list of each type of concrete with its cost
- links to the staff and customer pages
- The staff page should enable staff to query the database to retrieve a list of the jobs they have scheduled for a particular member of staff and month
- The customer pages should include:
- A page where a new customer can insert their details to be saved to the database
- A page where a customer can see any jobs they have ordered but which have not yet been completed
- Analyse the company situation
- a. What is the organisation’s general operation environment?
- b. What is its mission within that environment?
- c. What is the organisation’s structure?
- Define problems and constraints
- a. How does the existing system function?
- b. What input does the system require?
- c. What documents does the existing system generate?
- d. By whom and how is the system output used?
- e. Problem descriptions
- f. Constraint descriptions
- Define objectives
- a. Proposed systems’ initial objectives (how will it solve the problems identified above?)
- b. Will the system interface with other existing or future systems?
- c. Will the system share the data with other systems or users?
- Define scope and boundaries
- a. Extent of design according to operational requirements (entire organisation? One or more departments within the organisation? One or more functions of a single department?
- b. Boundaries:
- i. Time
- ii. Budget
- iii. Hardware
- iv. Software
- Data analysis and requirements
- a. Information needs
- b. Information users
- c. Information sources
- d. Information constitution
- Entity relationship modelling and normalisation
- a. Identify, analyse and refine the business rules
- b. Identify the main entities
- c. Define the relationships among the entities
- d. Define the attributes, primary keys and foreign keys for each of the entities
- e. Normalise the entities
- f. Complete the initial ER diagram
- g. Validate the ER model against the end users’ information and processing requirements h. Modify the ER model if necessary
- Data model verification
- a. Identify ER model’s central entity
- b. Identify each module and its components
- c. Identify each modules transaction requirements:
- i. Internal: updates/inserts/deletes/queries/imports
- ii. External: module interfaces
- d. Verify all processes against system requirements
- e. Make all necessary changes suggested in previous step
- f. Repeat b-e for all modules (unlikely to be required as this is really a single-module databse)
- Distributed database design (not required)
- Map the conceptual model to logical model components
- a. Map strong entities
- b. Map supertype/subtype relationships
- c. Map weak entities
- d. Map binary relationships
- e. Map higher-degree relationships
- Validate the logical model using normalisation
- Validate the logical model integrity constraints
- a. Attribute domains
- b. Constraints as appropriate
- Validate the logical model against user requirements
- a. Validate all logical model definitions against all end-user data, transaction and security requirements
- Define data storage organisation
- a. Determine location and physical storage organisation for each table (not required)
- b. Identify indexes and type of indexes to be used for each table
- c. Identify views and the type of view to be used on each table (not required)
- Define integrity and security measures (not required)
- a. Define user and security groups and roles
- b. Assign security controls
- Determine performance measurements (not required)
- a. Fine-tuning DBMS and queries to ensure they will meet end-user performance requirements
- Install DBMS (not required)
- Create Database (not required)
- Load or Convert the data
- a. Create tables
- b. Create indexes
- c. Insert data
- Test of database to ensure it maintains the integrity and security of the data (not required)
- Ensure queries retrieve required data
Your submission should be in the form of a number of files:
- A single Microsoft Word or PDF document including the following:
- a. Database initial study - this should include some information taken from the BACKGROUND section above
- b. Database design - this should include all of the stages shown above for Conceptual, Logical and Physical design and including:
- i. all necessary business rules
- ii. lists of entities/ attributes/domains/relationships/constraints
- iii. ER diagram(s)
- iv. relational schemas and dependency diagrams
- v. index specifications
- vi. any other required items
- A single text file containing SQL statements copied from MySQL:
- a. Implementation and Loading
- i. SQL statements used to create each table/index/view
- ii. SQL statements used to insert the required data
- iii. SQL statements for each query, Note that testing and evaluation requires you to run each of the required queries against the database to ensure that they function with the design you have made.
- a. Implementation and Loading
- A series of text files containing HTML, CSS and/or PHP code to implement your website. Make sure that the links between pages use the filenames which you use when you submit your files or they will not work.
Your submission document filenames should be in the following format replacing [student ID] with your 9 digit student ID number:
- BI5675_[student ID]_Report (PDF format is recommended)
- BI5675_[student ID]_SQL.txt (text file to include all required SQL code)
- BI5675_[student ID]_HTML_1.html (html files for the website, there may be a number of HTML files as required with the number to be incremented)
- BI5675_[student ID]_CSS.css (css file if used)
- BI5675_[student ID]_PHP_1.php (php files for the website, there may be a number of PHP files as required with the number to be incremented)