Summarize the key benefits that the database approach to data management offers over traditional file-based data management: Computer Science Assignment, UOL, UK

A small PC Building and retailing company. They build and supply custom desktop PCs, mainly for workstation use but also for gaming. They sell full systems, but also provide components for users to build and upgrade their own machines (CPU, GPU, motherboards, memory, PSU, etc.).

Their system builds are charged at the sum of the component price, plus 20% as a build and testing fee. They source the components directly from a number of manufacturers and some other stockists. As they often supply to business clients, prices must be provided both including and excluding VAT.

Each order they take is assigned to a specific member of staff who takes responsibility for the order from inception to delivery and support/aftercare. If the company staff is at capacity, they subcontract to a number of individuals, who receive half of the build and testing fee. The details for the staff, their workloads and the work that is contracted out are all stored in a back-end system.

The company is closing its retail front and from now on will operate solely by mail order via their new website. This website will have a rating and review system for the products they sell. They wish to incorporate as much of the above functionality as possible into a single system, supported by a single database.

Final Point The organization needs a database to run all the above operations. Your job is to design and build this database. The above is simply an outline of the company and you will need to make your own assumptions and interpret or even extend the scenario as you go. Use your imagination as you see fit, but you must clearly document all assumptions and extensions.

Question 1 

Summarize the key benefits that the database approach to data management offers over traditional file-based data management. Answer with regards to this case study.

List and briefly describe the major features to be found in a commercial database management system (DBMS).

Question 2

Develop a semantically rich data model that captures the above scenario in the form of an entity-relationship diagram (ERD). You should note (and number) all assumptions you make about the data and the reasoning behind your design choices. Also, include (and number) any appropriate constraints and a list of entity types showing their attributes and identifiers.

Question 3

Once you are satisfied that the ERD is a good representation of the organization’s data requirements, produce a logical design by mapping the ERD to a set of normalized relations – to third normal form (3NF). You should annotate and explain this process.

Question 4

(Covers LO 3) Take each of the normalized relations and implement them as SQL tables using an appropriate DBMS – such as Oracle, MySQL or another one you have access to – and a series of CREATE TABLE statements. You must include all primary and foreign keys as well as any other table or column constraints you feel are appropriate such as NOT NULL, CHECK, UNIQUE and DEFAULT. Provide screenshots of the working code. Ensure your user name or some other distinguishing aspect is included in the screenshot to verify it is your code

Question 5

Using appropriate sample data and your own imagination based on this case study, populate your finished tables with at least 10 rows of data in each table. Provide screenshots of the working code. When done, display the full contents of each populated table to screen and take screenshots. Ensure your user name or some other distinguishing aspect is included in the screenshot to verify it is your code.

Question 6

To demonstrate that your final database is useful, write a set of realistic sample SQL queries based on the above scenario (use your imagination for details of each query) but they should include the following techniques:

• SELECT…FROM…WHERE… • Joins (using two, three or more tables)

• Ordering output (ORDER BY)

• Grouping output (GROUP BY)

• Aggregate functions (MIN, MAX, AVG, COUNT, SUM) You should aim to write a minimum of ten sample queries – ranging from basic SELECT…FROM…WHERE queries to more advanced ones using the above techniques.

Be sure to fully evidence all SQL work by taking screenshots of the inputted code and the outputted results. Ensure your user name or some other distinguishing aspect is included in the screenshot to verify it is your code.

Answer

Request Answer of this Assignment