1IntroductionThis is an individual assignment, to be submitted electronically using the Canvas facility. A submission link will be enabled on Canvas closer to the submission date. It is due 23:59 Friday 24th August 2018, and contributes 20% towards the aggregate of 100 marks.The objective of this assignment is to reinforce what you have learned in the lectures and tute/ lab sessions. Specifically, it covers the basic concepts in the relational database model, and using entity-relationship model for database design.1.1PlagiarismAll code or other material that is not original must be fully credited. That is, any material that is copied or derived from another source must be clearly identified as such and the original author must be identified. Sometimes students assist each other with an assignment, but end up working together too closely, so that the students separate solutions have significant parts in common; unless the solutions were developed independently, they are regarded as plagiarised.Plagiarism is a very serious offence. Any submissions determined to be a result of plagiarism will be deemed as an academic misconduct and harsh penalties apply. It is also an offence for students to allow their work to be plagiarised by another student. You should familiarize yourself with the university website for Academic Integrity Policy, Procedures and Guidelines. (https://www.rmit.edu.au/students/student-essentials/rights-and-responsibilities/academic-integrity)All work is to be done individually and plagiarism of any form will be dealt with according to the RMIT plagiarism policy.1.2What to Submit, When, andHow1.2.1WhenThis assignment is due at 23:59 Friday 24th August 2018.1.2.2WhatYou should submit one PDF document with all answers together. You may use LucidChart to work on Part A of your assignment. You may use Word or any other word processor to compile your submission. At the end, convert it into PDF format. Do not submit Word files. if that option is not available on your system there are free pdf converters online you can utilise. e.g. http://convertonlinefree.com/1.2.3HowYou are required to submit your solution electronically using the Canvas facility. A submission link will be enabled on Canvas closer to the submission date.1.2.4Penalties for latesubmissionsLate submissions of assignments will be penalised as follows. For 1 to 5 days late, a penalty of 10% (i.e. 10% out of total marks, not 10% out of your marks) per day. For assignments more than 5 days late, 100% penalty applies. If you submit between 00:00 25 August and 23:59 25 August,your penalty is 10%. If you submit between 00:00 26 August and 23:59 26 August, 20% penalty, and so on.1.2.5SpecialConsiderationIf unexpected circumstances affect your ability to complete the assignment you can apply for special consideration. If you seek a short extension, you can directly contact the lecturer. For longer extensions, you must follow instructions provided at http://www1.rmit.edu.au/students/specialconsideration1.3 Preparation TasksFor Part A of this assignment, you must be familiar with LucidChart or a similar diagramming application. You are required to complete the Week 4 Tute/ Lab session prior to attempt this assignment. Additional resources on using LucidChart are available under Resources section of the course Canvas shell.2Part A: Entity-Relationship Modelling (70Marks)2.1Designing an Entity-RelationshipModelYou have just been employed as a database designer in a well-established software development firm. Your first job is to design and implement a database system for a small medical centre in one of the Melbourne suburbs.The following information have been gathered after analysing the current practices of the medical centre.·The system records information about the doctors work at the medical centre. Doctors have unique registration numbers. In addition to the registration number for each doctor, the system records the name, date of birth, gender, qualifications, and specialisations. Doctors can have multiple qualifications and multiplespecialisations.·The system keeps a record for each patient. When a new patient comes to the medical centre, the patient is given a registration form to be filled. The information gathered in this form will be stored on the system. When these information is entered to the system, a serial number is assigned to each patient. The registration form captures following information: name, address, date of birth, gender, telephone numbers (multiple numbers are allowed), Medicare number (if any), private health cover number (if any), and private health cover company (if any).·When a patient attends a consultation with a doctor, following information are recorded: consultation number, date, time, description, symptoms, and special remarks. Symptoms is a multi-valuedattribute.·A number of prescriptions can be recommended after each consultation. For each prescription, following information are recorded: name of the medicine, dosage, number of repeats, and special instructions. None of these attributes are unique. However, for a given consultation a medicine is prescribed onlyonce.·The doctors sometimes make referrals for other procedures, such as X-ray, MRI, and physiotherapy. The system should be able to record such referrals madeatconsultations. For each referral, following information are recorded: name of the procedure, short description, referred practitioner, and whether this procedure essential or optional. Like prescriptions, referral for a given procedure is only made once in a consultation·The system should be able to keep accounting information. In particular, the system should be able to store payments made for the consultations. There are three possibilities:1.bulk-billing:Some Medicare1patients (such as disabled and seniors) are bulk-billed. This means that the patient is not required to pay anything up-front. The medical centre will bulk- bill the Medicare (the government agency that deals with healthcare services in Australia) the costs associated with the consultation. For such transactions, the medical centre will record the payment number, timestamp, amount and the Medicare reference number2.full up-front payments (cash orcard):The patients with no Medicare benefits are required to pay full cost of the consultation up-front. For such payments, the medical centre will record the payment number, timestamp, the amount paid, and the method of payment.3.settling part of the invoice using a Medicare benefits and the balance in cash or card:Some Medicare patients are required to pay a top-up payment (gap fee). That means, part of the consultation fee is covered by Medicare benefits and the rest is paid up-front by the patient. For such transactions, two payment records are created and stored. (The both are linked to one consultation). First record will be similar to(1)above. The second is similar to (2)above.Based on the information you gathered, model the activities in your client s business and present your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you make. In your ER diagram, you must properly denote all applicable concepts, including weak or strong entities, keys, composite or multi-valued attributes; relationships and their cardinality and participation constraints.If you cannot represent any of these information in the ER model, clearly explain what limitations in the ER model restrict you from representing your model.You must use UML notation used in the lectures and tute/ lab sessions. You may use any diagramming tool to draw your diagram, however, use of LucidChart is highly recommended. Your diagram must be drawn to a high standard with minimal clutter.Note that you are not required to map the ER model to relational model.A special note: This is an open-ended question with many different models can be derived. Your model is assessed based on how accurately it represents business rules described above.1 If you are not familiar with how Medicare works, http://www.mydr.com.au/first-aid-self-care/australian-health-system-how-it-workshas a simpleexplanation. 2.2Designing and refining an Entity-RelationshipModelA small bus company wants a simple database to track some aspects of its operation. They describe key elements of their requirements as follows.·The company has busses that can be identified by their registration number and it is important to record the brand and model, year made, the number of seats, the engine capacity and the last servicedate.·Bus routes are known by their Route Number. Route numbers are three-digit numbers, starting with 100. All routes have a start terminus and an end terminus and the distance of theroute.·The drivers details are name, their start date with the company, their licence number and its expirydate.·Each bus driver is allocated a bus and only that driver will drive that bus. However, at any given time, some drivers will be on leave and some busses will be in forservicing.·A bus is allocated to just one route at a time. However, at any given time, there are number of busses plying on the sameroute.·Several mechanics work for the company. Their name and unique certificate number are stored in the database. A bus can be worked on by any of the mechanics and all mechanics are qualified to service allbuses.Based on the information you gathered, model the activities in your client s business and present your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you make. In your ER diagram, you must properly denote all applicable concepts, including weak or strong entities, keys, composite or multi-valued attributes; relationships and their cardinality and participation constraints.After presenting your ER model to the management, they pointed out that your model lacks the ability to capture all requirements of the operation.In particular, they pointed out the following shortcomings:1.For auditing purposes, it is important to keep track of allocation of drivers to busses. For each shift, it is required to record date, start and end times, the bus number and the driver allocated, and any remarks made by the driver at the end of theshift.2.For auditing purposes, it is required to keep track of allocation of busses to routes. For each allocation, it is required to record route number, bus registration number, date, start and end times, and any additional remarks made after the bus returning to thedepot.3.It is not sufficient to only record the last service date. It is required to keep track of all services done on all busses since they entering the service. For each service done, it is required to keep following information: bus registration number, service date, service type (major or minor), odometer reading, and any additional remarks made at the service. It is also required to identify the mechanic responsible for eachservice.Draw a modified ER diagram to accommodate these additional requirements. Noe: Your answer to this question should include TWO complete ER diagrams.2.3Mapping an ER Model to a Relational DatabaseSchemaConsider the following ER diagram, which models an online bookstore. Map this ER diagram into a relational database schema. Show every step of the mapping. No marks are awarded to the final schema if you do not show the partially-built schema at the end of each step. Indicate the primary key (underlined) and foreign key (with an asterisk) in each relation.3Part B: Relational Database Model (30Marks)This question has been adopted from Fundamentals of Database Systems, Elmasri and Navathe. (Question 5.11).A relational database schema and an instance of this schema are given below.Most of the attribute names are self-explanatory. Super_SSN refers to corresponding employee s supervisor s SSN (Social Security Number). This example is based on US system, assume it is similar to Australian Tax File Number.Arrows indicate foreign keys and the corresponding attributes in parent relation. In the case of Super_SSN, the parent relation is the Employee relation itself (self-referencing).1.It was found that the data entry operator had incorrectly entered data for John B. Smith and Ramesh K. Narayan. Their SSN were swapped. The error was discovered, and the data entry operator was instructed to fix it. (S)he attempted to swap SSN value in each tuple using the following SQLstatement.UPDATEemployeeSETSSN=’666884444’WHERESSN=‘123456789’;UPDATEemployeeSETSSN=’123456789’WHERESSN=‘666884444’;But, it was not successful. The DBMS returned an error message and two tuples were not updated.a)Explain why they didn’twork.b)Give another way to correct theerror.c)Write down the correct SQL UPDATE statements to carry out your proposedsolution.2.James E. Borg gets a pay rise, which increased his salary by 10%. The data entry operator has executed the following SQLstatement.UPDATEemployeeSETsalary=salary*110/100WHERESSN=‘888665555;Write down all the integrity constraints violated by the above operation. If the operation does not violate any constraints, indicate as no violations .3.Due to a workplace dispute, John B. Smith has been moved to a new supervisor. To effect this change, the data entry operator has executed the following SQLstatement.UPDATEemployeeSETsuper_SSN= 666884444 WHERESSN=‘123456789 Write down all the integrity constraints violated by the above operation. If the operation does not violate any constraints, indicate as no violations .4.A new project has been established, but neither a project number nor a department is assigned yet. A new record has been entered into the Project relation, asfollows.INSERTINTOproject(pname,plocation)VALUES(‘AgentX’,‘Washington’);Write down all the integrity constraints violated by the above operation. If the operation does not violate any constraints, indicate as no violations .代写CS Finance|建模|代码|系统|报告|考试编程类：C代写,JAVA代写,数据库代写,WEB代写,Python代写,Matlab代写,GO语言,R代写金融类：统计，计量，风险投资，金融工程，R语言，Python语言，Matlab，建立模型，数据分析，数据处理服务类：Lab/Assignment/Project/Course/Qzui/Midterm/Final/Exam/Test帮助代写代考辅导天才写手,代写CS,代写finance,代写statistics,考试助攻E-mail:[email protected]微信：BadGeniuscs 工作时间：无休息工作日-早上8点到凌晨3点如果您用的手机请先保存二维码到手机里面，识别图中二维码。如果用电脑，直接掏出手机果断扫描。

所有的编程代写范围：essayghost为美国、加拿大、英国、澳洲的留学生提供C语言代写、代写C语言、C语言代做、代做C语言、数据库代写、代写数据库、数据库代做、代做数据库、Web作业代写、代写Web作业、Web作业代做、代做Web作业、Java代写、代写Java、Java代做、代做Java、Python代写、代写Python、Python代做、代做Python、C/C++代写、代写C/C++、C/C++代做、代做C/C++、数据结构代写、代写数据结构、数据结构代做、代做数据结构等留学生编程作业代写服务。