MSCI520 DB代写、代做DB knowledge、代做SQL程序、SQL语言代写

日期：2018-11-15 10:23

MSCI520 DB 2018-19 – Assignment - Submit by Monday 19th Nov 2018 4pm (Week 7)

- There are four tasks in total: three are practice-based (Tasks 1-3) and one is about general DB knowledge

(Task 4). Each task is broken down into subtasks. You need to complete all subtasks.

- Tasks 1-3 count for 50% of your marks and Task 4 for 50% of your marks

- This assignment counts for 50% of the MSCI520 module.

TASK 1 (Practical): You are asked to design a database for a Medical Service practice:

a) Produce one Entity Relationship (ER) diagram to show the database design you would propose. This should

include all the entities and relationship you need. Each entity should have all the attributes you want to

record. The cardinality (1 to 1; 1 to N; and M to N) of all relationships should also be indicated. You also

need to identify a (primary) key attribute for each entity. You can use Lancaster Medical Practice as an

example http://www.lancastermedicalpractice.co.uk/

NOTES

Keep it simple (e.g. < 5 entities!) but try to include relationships covering all the three different cardinality

types.

Use any appropriate graphics tool choice but submit as ppt or pdf

You can add a short (max 150 word) description of your diagram if you wish

Score: [10]

b) From the ER diagram above select two entities in a many to many (M to N) relation (e.g. many patients have

booked many medical appointments). Translate this portion of your ER diagram into a Relational data

model. Note down the structure (schema) for each table.

c) Login in to http://scc-webtech.lancs.ac.uk/phpmyadmin/ set up the structure for each of the three tables

using phpmyadmin

Don’t forget the primary key in each table

Don’t forget the foreign keys in the association table

Add up at least five records in each table

Make sure to save the tables

Try to set up relations between the tables using the ‘Designer’ tool in phpmyadmin

d) Write an SQL query that retrieves all items from the association table; include your SQL query and its results

in your DB assignment report.

NOTES

As a result, you should have two entity tables and one association table in your MySQL database

https://www.tutorialspoint.com/dbms/er_model_to_relational_model.htm

Score: [10]

TASK 2 (Practical): Download the Fortune 500 dataset from Moodle - Write the SQL statement for each of the

following queries:

1. Find all the companies that are in the technology sector

2. Count how many companies headquarters are in New York state

3. Find all the companies whose revenue is less than 10 billions

4. Find the company with the highest assets

5. Find all the companies where the revenue is more than 10 billions AND are in California

6. Calculate the total revenue for all companies

7. Find all companies based in California OR Texas

8. Find the company with the highest revenue and the smallest number of employees

Score: [15]

TASK 3: Set up the structure for the Fortune 500 table and import the file using phpmyadmin; run the queries

above; write down the results for each of the above queries

NOTES:

- Submit your queries AND the results of the queries as a list in a pdf format

- For example use this format:

QUERY

number

SQL CODE Results

1 Write your SQL code here Write the result here – this can be a single item

or a list; no need to include results of Query

number 9 and 10 because they will show in the

MySQL table

Score: [15]

TASK 4 (DB general knowledge):

1. You are an online adventure sports retailer and your business is scaling up rapidly. To further harness its

success, you plan to introduce an additional service where your customers can add ratings, open-text

reviews, photo and video uploads of the products purchased and the context of their use (e.g. holidays,

races, etc). What DB considerations should be made when introducing these new features? In particular,

think about the choice of a database system that can handle scale whilst improving user experience. For

example, reflect on the pros and contras of choosing a DB management system that follows BASE or ACID

rules.

Score: [30], write your answer in 300-500 words

2. What is the role of a primary key in a database and why is important?

Score: [5], write your answer in 80-150 words

3. What is the key difference between SQLlite and most RDBMS?

Score: [5], write your answer in 80-150 words

4. What types of malfunctions can affect a RDBMS and how can they be addressed?

Score: [5], write your answer in 80-150 words

5. What is normalisation in a RDBMS and why is important?

Score: [5], write your answer in 80-150 words