Designing a database from the problem

Reference no: EM133688517

Objective - Demonstrate your knowledge and understanding of database and website design.

Problem Description
You work for a manufacturing company that specializes in producing a variety of products. You have been contracted to design a simple database system that effectively manages products, ingredients, and suppliers for this manufacturing company.
The company produces various products, each composed of specific ingredient(s) from different supplier(s). Each product has name and price. The company also wants to maintain each supplier's contact information such as name, address, and phone number for future use. A supplier can supply multiple ingredients. An ingredient can be supplied by one supplier. Each ingredient has a name and price per kg. The system should also keep track of the ingredients used (in kgs) for each product.

Task 1: Designing a database from the problem description

Designing a database consists of 4 basic steps.

Task 1.1: Complete the following questions according to the problem description
Identify the possible entities in the problem description.
Identify the attributes in the problem description.
Identify how the entities relate to each other (the relationships)
Identify (if any or create) attribute(s) that could be used as primary keys for each entity.

Task 1.2: Create an ER diagram
You can do this in any program of your choice (e.g., MS Word, draw.io (online), etc.). Either paste the diagram directly or take a screenshot of your ER diagram and paste it your word document. Your ER diagram should have all the entities, attributes, relationships, primary keys (underlined) and cardinalities (1-1, 1-M, M-1, M-M).

Task 1.3: Convert the ER Diagram into tables
For this task, you are not required to implement the tables in any RDBMS. You are just required to write the schema, i.e., table name, attributes, primary keys, and foreign keys, as shown in the lectures/labs.
This is a step-by-step process:
Create one table per entity.
Each attribute of the entity is represented as a column in the table.
Define the primary key columns (underline them)
Add the foreign keys (write them in italic).

Task 1.4: Create tables using SQL
Create all the tables you have in Task 1.3 using SQL. Paste your queries in your word document. Your table creation should consider any primary keys/foreign keys.

Task 2: SQL Queries

With SQL, how can you return all the records from a table named "Suppliers" sorted Z to A by "FirstName"?

Write SQL query to select all the suppliers where their last name contains the letters "eo" and starts with a "J".

Consider a database table named "Supplier" with a single column "LastName" shown below.
Galland
Klingman
Melancon
Gess
Dones
Lamborn
Elizondo

Consider the SQL:

SELECT LastName FROM Supplier
WHERE LastName LIKE "%o%"
ORDER BY LastName
LIMIT 3 OFFSET 1

Write down the order each name will appear in the result. Mention "will not appear" next to a name if it won't appear in the result.

Task 3: Wireframe User Interfaces

You are to design wireframes for a product manufacturing company like problem description but not limited to the problem description.
Please consider what different pages you can use for your website design. Some examples can be Homepage, Dashboard/Analysis, Product List, Ingredients List, Add/Edit Suppliers etc. You can visit available similar websites for any manufacturing companies for your reference. You can think about the pages according to the problem description and add any additional details you may find relevant.
Note that you can wireframe the UIs according to your understanding and preference. We are looking for creativity for this task. You are required to at least have minimum of 5 different pages for your UI. You also need to show flow/interaction among different pages. You need to use Figma introduced in the lab to create your UI.

Share link of your work in word document, make sure the link is accessible to the teaching team - Huawei and Aseel. You can copy the link from the address bar while you're in "Present" mode, and paste it in your word document. DO NOT send the invite by email. DO NOT share your link with anyone else in class.

Reference no: EM133688517

Questions Cloud

How did ordinary soldiers impact the war : Did some people oppose slavery? What did they do about it? What roles did enslaved people play in the revolution? How did ordinary soldiers impact the war?

Was there opposition to the new government after the war : Was there opposition to the new government after the war? How?

Analysis of the texts affordances for early years literacy : Analysis of the texts affordances for early years literacy and understanding of childhood development in the early years. Specific strategies for supporting

Explain the significance of the fall of soviet communism : In 100 to 150 words, explain the significance of the fall of Soviet Communism, starting with the fall of the Berlin Wall in 1989.

Designing a database from the problem : CSE4IFU Database and Web Design, La Trobe University - Implement the tables in any RDBMS. You are just required to write the schema

Discuss the theme of neoclassicism : Discuss the theme of Neoclassicism and fashion that was very important to the decade, especially the first half of the decade.

What does it tell us about roman society : What does the graffiti tell us about what life in a Roman city could be like? What does it tell us about Roman society?

What did jefferson mean by the statement in his inaugural : Called the "People's President", what did Jefferson mean by the statement in his inaugural address: "We are all Federalists.

Check that the neighboring relationships have been created : Check that the neighboring relationships have been created between R1 and R2. Check that, after configuring OSPF, you can ping

User Account

All Pages