Implement in SQL the following table schematics

Module Learning Outcomes Assessed:
1. Demonstrate a clear understanding of organisational structures and an appreciation of the role of information technology in the support of business systems, including an awareness of the ethical, social and legal principles in the professional context.
2. Demonstrate an understanding of the methods and techniques involved in the design and implementation of various types of information systems with justification behind appropriate selection.
3. Apply appropriate methods for the initial analysis of information processing systems, developing a diagrammatic representation of system requirements using selected modelling techniques, supported by the use of a CASE tool.
4. Understand how enterprise systems foster stronger relationships with customers and suppliers and how these systems are widely used to enforce organisational structures and processes.

Task and Mark distribution:
Answer ALL the questions.

1. CASE tools are commonly used when developing business systems. Research and discuss which CASE tools are available for project teams and how are they used today in industry.
(20 marks)
(Word Count: 500 words)

2. There are several methodologies, which can be used for information systems development ranging from Waterfall model to Agile development. Each methodology uses different tools and techniques for modelling. In detail describe and discuss your choice of methodology in a situation where the requirements from your client can change as they operate in a dynamic environment. Support your answer with a justification of why you have selected the methodology– a brief history, who and how it was developed; the main principles; resources required (people and IT); the drawbacks of the methodology; and any additional information you consider important.
(20 marks)

(Word Count: 500 words)

3. A local Pharmacy is looking to expand operations by developing a website that will market the business and enable customers to manage their prescriptions. The system would host information about Patients, Doctor, prescriptions, aliments/allergies and record log of issued medicine and payment. Develop a preliminary Class Diagram. The diagram should include classes, attributes, operations and associations. This MUST be completed in StarUML or a suitable CASE tool.
(30 marks)

4. Implement in SQL the following table schematics. The product detail is of your choice. You will be expected to enter fictitious data for each table.

Member(MemberNo, Name, Address, TelephoneNo, *OrderNo)
Order(OrderNo, Date, DeliveryAddress, Quantity, TotalCost, *MemberNo)
OrderLine(*OrderNo, *ProductNo, Quantity)
Product(ProductNo, name, QuantityInStock, UnitCost, *SupNo)
Supplier(SupplierNo, Name, Address, TelephoneNo)
a. Create the above tables in SQL. (10 marks)
(Note: underline refers to a Primary Key and * refers to a Foreign Key.)
b. Evidence of 10 records in each table. (5 marks)
c. List of all members who have placed an order after 06/06/2018. (5 marks)
d. The total cost of orders placed whose product was supplied by Handle LTD. (5 marks)
e. List the most popular product ordered by customers in the past year. (5 marks)

For task 1 and 2 ensure you reference appropriately using CU Harvard standard.

To achieve above 70%
Excellent grasp of relevant concepts illustrated.
This will include correct and clear use case diagram showing the functionalities of the system. Correct class diagram complete with all classes, attributes, operations, associations, association names and multiplicities. Show evidence of correct SQL commands with screenshots of the output.
No serious flaws in solution.
Professional quality product, discussion and documentation of design process
Evidence of application of own original ideas to problem solving All handing in requirements met
To achieve above 40%
Understanding of some of the relevant concepts illustrated
Some serious flaws in solution
Poor documentation format, brief but sound coverage of design process
Evidence of application of taught material to problem solving
Most of handing in requirements met

Below 40%
Poor understanding of the relevant concepts demonstrated
Many serious flaws in solution
Poor standard of documentation and explanation of design process
Lack of understanding of how to apply taught material to problem solving
Little of handing in requirements have been met

Format & Guidelines
• The written report should have a clear structure, which is evident through the use of a numbering system and/or styles of headings.
• It will be necessary to organise ideas and plan this structure in advance of writing the detailed content.
• Do not forget to include a word count.
• Electronic submission only. Upload directly into Moodle before 03/08/2020 @ 6pm.
• You must check your assignment for similarity in Turnitin before you submit. If the similarity is greater than 20%, edit your work before submitting into Moodle.
• Paper submissions will not be accepted.
• File name format: Familyname-Firstname-104KM.pdf or
in .doc or .docx or .pdf file format.

Notes:
1. You are expected to use the CUHarvard referencing format. For support and advice on how this students can contact Centre for Academic Writing (CAW).
2. Please notify your registry course support team and module leader for disability support.
3. Any student requiring an extension or deferral should follow the university process as outlined here.
4. The University cannot take responsibility for any coursework lost or corrupted on disks, laptops or personal computer. Students should therefore regularly back-up any work and are advised to save it on the University system.
5. If there are technical or performance issues that prevent students submitting coursework through the online coursework submission system on the day of a coursework deadline, an appropriate extension to the coursework submission deadline will be agreed. This extension will normally be 24 hours or the next working day if the deadline falls on a Friday or over the weekend period. This will be communicated via email and as a CUMoodle announcement.

Last Completed Projects