MySQL Security Practices - Assignment Example

? Analyze and design a small database application based on the given case study: ANALYSIS AND DESIGN OF GARAGE SCENERIO DATABASE Question 1 Interview I interviewed the following categories of groups who will be interacting with the Mercedes garage system. Customers- I obtained the following information from the customers; the customer takes the car to servicing after the computer at car’s dash board notifies him/her that the car should be taken to servicing. The customer can take the car to any nearest branch of Mercedes Company for servicing and he/she expects to be called to come and collect the car after service and given an invoice of all the cost incurred during the service. Mechanics-I also interviewed mechanics for one of the branches for Mercedes Company and obtained the following information, there is a chief mechanic whose work is to issue jobs at the garage; he can issue a service job to one or many mechanics at the garage depending on the magnitude of the job. There sometimes when some Mechanics are off duty thus they are not issued any job. A mechanic records parts to be bought and the actual cost of labor and notifies the customer care department so that the department can contact the relevant customer. Customer care – after a service has been done, the customer care depart contacts the owner and sends him/her and invoice as per the service done. Brainstorming I would hold a meeting which would have the representation of all the parties, stakeholders and departments involved. The meeting would have a director from the Mercedes Company, a few customers from different branches of the Mercedes Company, a few mechanics, chief engineers and customer care employees from different branches of the Mercedes Company. We would brain storm on how we want the system work in order to fully satisfy the needs of the involved people. Secondly I may issue questionnaire to the people in the meeting so that they fill so as I get to know how each person who works in the service wants his/her work simplified. After interviewing the involved parties and brainstorming I should research for on similar garage databases and obtain the requirements that are behold the user’s perception. Question 2 Find the diagram attached in another file. Question 3 Customer table Table name Attribute Data type Length Domain Primary key Foreign key Null customer CusNo int 10 yes no customer FirstNames varchar 50 no customer LastName varchar 50 no customer Location varchar 100 no customer Mobile varchar 100 no customer Email varchar 100 yes customer passportNo varchar 50 yes Customer S.CenterNo int 10 yes Car table Tablename Attribute Datatype Length Domain Primary key Foreign key Null Car RegNo varchar 50 yes no Car carname varchar 100 no Car model varchar 100 no Car Make varchar 100 no Car Datepurchased date no Car showroomNo int 10 yes no Car cusNo int 10 yes no Showroom table Tablename Attribute Datatype Length Domain Primary key Foreign key Null Showroom roomNo int 10 yes No showroom roomName varchar 50 No showroom location varchar 100 No showroom telephoneAddress varchar 100 No Company details Tablename Attribute Data type Length Domain Primary key Foreign key Null company companyNo int 10 yes No company name varchar 50 No company logo image 16 No company location varchar 100 No company webSite varchar 100 Yes company EmailAddress varchar 100 Yes company imageFile varchar 250 No Garage details Tablename Attribute Data type Length Domain Primary key Foreign key Null Garage GarageNo int 10 yes No Garage name varchar 50 No Garage logo image 16 No Garage location varchar 100 No Garage telephoneAdd varchar 100 No Garage EmailAddress varchar 100 Yes Garage RoomNo int 10 yes Service details Tablename Attribute Data type Length Domain Primary key Foreign key Null service ServiceNo int 10 yes No service DateofService date 50 No service TypeOfService varchar 200 No service PartsReplaced varchar 100 yes service Labourcost varchar 100 No service partsCost varchar 100 yes service CusNo int 10 yes Employee details Tablename Attribute Data type Length Domain Primary key Foreign key Null Employee EmployeeNo int 10 yes no Employee Names varchar 50 no Employee jobtype varchar 100 no Employee IdNo varchar 100 no Employee location varchar 100 no Employee Email varchar 100 no Employee serviceNo int 10 yes Booking details Tablename Attribute Data type Length Domain Primary key Foreign key Null Booking bookcardNo int 10 yes no Booking Bookdate varchar 50 no Booking location varchar 50 no Booking CusNo int 10 yes no Booking Howlongbooked int 10 no Parts Tablename Attribute Data type Length Domain Primary key Foreign key Null parts partNo int 10 yes no parts partname varchar 50 no parts partcost float 100 no parts RegNo varchar 50 yes no Notification Tablename Attribute Data type Length Domain Primary key Foreign key Null Notification NotNo int 10 yes no Notification NotDetails varchar 50 no Notification CusNo int 10 yes no Notification RegNo varchar 50 yes no Status Tablename Attribute Data type Length Domain Primary key Foreign key Null Status Not started varchar 50 yes Status started varchar 50 yes Status complete varchar 50 yes Status RegNo int 10 yes no Question 4 Assumptions made when creating ERDS and Data Dictionary The showrooms can have one garages but it is a must for it to have one garage for servicing the cars. At a showroom booking of car details for repairs from a customer is done. Many cars from different customers can be booked at a showroom for service. Service jobs are done by mechanics, a job can be allocated among one to three mechanics, there is at least a time when a mechanic is not allocated a service job. Servicing jobs are done on the garage, servicing of different cars can be done on the garages but there sometimes when there are no cars being serviced at the garages. Customer contacts Service center to know about the date of collection of his car after servicing. Service center can be contacted by many customers. A customer can book his/her car for service at different showrooms. The duration for an every Mercedes car to be serviced from the last day serviced is the same (Chris, 2003). Customer can be can be a co-operate company or an individual. Service job may not require part replacement or may require one or many parts to be replaced. A car can have no faulty, one or many faulty parts. A show room has one customer care center but it is a must that it has one. A customer must receive one receipt which is generated from a booking for any car booked. A garage can have a maximum number of 20 mechanics but it is a must it has a mechanic. A maximum of 5 staffs can administer on a booking but it is a must that one administers a booking. It’s optional that a mechanic replaces a faulty part because not every car has a faulty part (Chris, 2003). A booking includes a garage details, And a service book-contains services to be done. N/B have not changed no 5 because I have that it is in accordance to the tutorials you send me. It applies transaction as explained there –“select*,from table ;and terminates with a semi colon; Question5 Sql statement for viewing the total number of booking SELECT* FROM Booking WHERE bookdate =’03/06/2012’; N/B the date used is just an assumption that the record is already in the database. The above statement is used mainly to display the all the booked cars and customers in a garage in a given day so that the managers can allocate different tasks efficiently. Sql statement for daily report SELECT car.carname,car.model,status.jobstatus FROM car INNER JOIN status ON car.RegNo=Status.RegNo WHERE status.jobstatus=’complete’ GROUP BY car.model; N/B this is with the assumption that “complete” is already recorded in the database. These query can be implemented using report creating tools e.g. crystal report writer to help generate a report to the service center department for them to notify customers about the service jobs that are already complete. Invoice sql query SELECT DISTINCT ROW Customer.FirstName+’ ‘+Customer.LastName AS customernames,car.carname,car.RegNo,part.partname, parts.partcost,service.labourcost,SUM([parts.partcost]+[service.labourcost]) AS servicecost FROM Customer INNER JOIN(service INNER JOIN(parts INNER JOIN service ON service.serviceNo=parts.serviceNo) ON car.RegNo=partNo.RegNo) ON car.CusNo=Customer.CusNo WHERE ((Customer.CusNo=’10’) AND (Date=’3/06/2012’)); N/B these statement is based on assumption that cusno 10 and date 3/06/2012 is already in the database. These sql statement is used to joins three tables inorder to retrieve customer names,carname partsname and their costs labourcosts and sums them up as total cost that the information obtained can be used to write an invoice to the customer. Sql statement for calculating the numberof booked vehicles SELECT model,COUNT(RegNo) AS Carcount FROM Car GROUP BY model ORDER BY COUNT(RegNo) DESC; This sql statement counts the number of different models of Mercedes cars that are brought for service and returns the total count in each category. QUESTION 6 Identify who the users of this database might be, how they would use the database, and provide a discussion of the roles and privileges you might create. What steps would you take to ensure security of the data in the database? To be precise, the users of this database are the end users who are the guest users and the administrators of the database, who happen to control the system. Take for instance, the end users or who can be termed as guest users use the database for retrieval of data, but the vital data for the running of the system might solely be controlled fully by the administrators. Guest users only have partial rights to the database. This is to safeguard the guest users from tampering and changing critical data which help the database to be secure and run smoothly (Alfred, 1986). Roles and privileges that might be created A use who can be newly created can log into the MySQL server, but by default has zero privileges to perform anything once connected. The next task upon creation of a new use account, is to add privileges to that account. This can be attained using the GRANT statement. Hence, before even altering a user’s privileges, it can be useful to observe what privileges might be set. This can be done using the SHOW GRANTS statement in relation with the user’s account name (Beck, 1999). One can encounter the statement USAGE ON*.*, which shows that the user has zero privileges on any database or table, meaning that the user cannot carry out any functions once logged into the server. To add a privilege, for instance that one involving permission to query any table in a database, one must utilize certain command. Upon execution, the command would enable the user to perform SELECT statements on any table contained in a chosen database file. We hence would enable that specific file to INSERT rows into a table called product contained in the garage database. Moreover, one can even specify multiple privileges in a single GRANT statement. In summary, MySQL supports a wide range of privileges such as ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, among many others (Beck, 1999). Steps used to secure data in the database 1. Removal of wildcards in the tables of grants. While grant tables permit administrators to set blanket kind of allowances for a user or even a set of tables using wildcards, hence it is harmful since someone could utilize a single compromised account to enter various parts of the system. Hence one must be brief during assigning users’ privileges. 2. Requiring the usage of secure passwords. User’s account should only be safe as the passwords used for their protection one must assign a solid password to the MySQL root account during the installation process. 3. One must ascertain the permissions of configuration files. In order to make server connections to be fast and reliable, both the individual users and server administrators keep their user account passwords in the per-user options file. The file should be stored in plain text that can be easily read. Furthermore, one must ensure that such files cannot be observed by other users. 4. Encrypt client-server transmissions. A vital issue in the MySQL client-server structure is the security of data transmitted on the network. The transaction must not take place in plain text because one would hack. The gap can be closed by activating SSL or by utilizing a secure utility such as Open SSH to make encrypted passage. 5. Disable remote access. One reduces the hazard of a network attack by coercing MySQL connections to occur through UNIX file. It can be done via initiating the server with the skip-networking option. This would hinder TCP/IP network connections occurring to MySQL (Philip, 1992). Question 7 Date Type of test Reason Data Expected results 4/08/2012 To 13/08/2012 Input data into Employee, customer, car, service and showroom forms Check validity and Error detection Check if data exceeds and truncates names. Max length. Min length. Incorrect data. Correct alphanumeric. Handling of null values. Correct – form should submit and data should be correctly updated to the data fields. Incorrect- appropriate error message should be generated. Ability to recover from Error 16/08/2012 To 23/08/2012 Check the out puts in the generated reports. To compare the output of the reports with the expected output. Check arithmetic calculations give correct outputs. Check if sql statements retrieve the required data. Check referential integrity points and cascading deletes. Correct- the reports generated should be represents the excepted results. Referential integrity is well implemented between the tables. Ability to recover from Error 25/08/2012 Data Back-up ability Check if the database is able to take back-up Back-up of data is automatically taken by the database after a specified amount of time. Data-back up can be taken manually when needed Correct- Back-up should be taken automatically after a specified duration of time. It can also be taken manually when required QUESTION 8 Decide on the best implementation method for the new system. Justify the approach. I choose Pilot changeover to implement Mercedes garage system. It involves implementing the complete system at the selected location of a company and since Mercedes company has many branches we can implement it in one of the branches. If the implemented new system runs properly as expected, then it is subsequently implemented to all the other branches of the Mercedes Company (Erich, 1996). Reasons are as follows Pilot change over is appropriate because it reduces the risk of system failure compared to the direct change over which involves to changing implementing new system to each branch of a company. If a system fails in pilot site branch it will only affect that branch and not any other branch. Pilot change over is appropriate because is less expensive compared to parallel change over which requires that both the new database garage system and the old garage operating processes operates together for a specified period of time. Thus pilot change over is cost effective way of implanting the garage database system. Pilot system gives time to me time as a developer to adjust the system to cater for all user requirements that might have been left out during system developed as well as to the user to bring up any new requirements. Lastly it is appropriate to implement garage database system in pilot change over because it is a small system and not advisable to implement it through phase change over which is used to implement large business application systems. Question 9 Reflective summary of this project The project concerned analyzing and designing a small database application of a garage system. As it is evident above, after my programming, there is entity relationship diagram that became generated. The diagram serves as very strong basis of the crucial foundation that became accorded for this small project (Erich, 1996). It is evident that nowadays, MySQL is one of the top databases available in the world. This is because it has many worthy features and options. It can be proved that is indeed fast, reliable, cost friendly and affordable in terms of maintenance. The benefits are as follows: 1. Open source. MySQL can ne rightly considered to be an open source database management system, which means that anyone can utilize it for free. Manufactures can amend its code to suit such requirements, meaning that it is customizable. Moreover, it is available in the market with no costs of ownership. 2. Aspect of fast development. Various numbers of people in the world are continuously developing new modules for integrating with MySQL. This shows that it has a wider and faster path of development. 3. Reliable for small businesses. By virtue of it being free, it means that overall costs can be reduced for small businesses. It is easy to learn and operate, hence lower operational costs. 4. Cross platform operability. It can easily be installed and operated on different platforms such as Windows, Linux, and even Solaris. This makes it a favorable choice for companies that are developing. In summary, this is what I have managed to grasp during the learning process of creating, analyzing and designing a small database application for a garage system, QUESTION 10 Professional issues considered when undertaking this project Just to make things clear, a professional IT practitioner, is someone who is solely responsible for reviewing and building the organization’s IT service lifecycle. It can be considered to be program management position, and tends to work within many domains such as service strategy, service design, service transition, service operation, and continual service improvement. He or she must be highly qualified in the IT field and all its aspects (Bill, 2007). Furthermore, an IT practitioner should behave ethically towards his or her customers. He or she should be guided by the professional code of ethics of IT practitioners towards relating with the customers. He should differentiate between acceptable and unacceptable behaviors in a given situation. He or she should be honest and accountable to his or her clients. He or she must practice data privacy and keep customers’ information secret and secure. He or she should not expose or use customers’ data or documents in any external transactions. He or she should be fair in the way of treating customers’ interests. He or she should not be biased in the making of decisions when confronted with challenging issues concerning various customers during the tenure of the contract in the making of this project. Therefore, ethics towards the customers should be highly regarded in every day of doing projects for the clients. Suchvirtues are what builds the good name of a business in the long run (Bill, 2007). References Beck, K (1999). Extreme programming explained. London: Addison-Wesley publishers. Alfred, A (1986). Compilers: principles, techniques and tools. New York: Addison-Wesley Publishers. Scott, M (1997). Effective C++: 50 specific ways to improve your programs and design. London: Addison-Wesley Markus, K (1988). A summary of the ISO EBNF notation. Milan: Oxford Press. Chris, F (2003). An introduction to DB@ UDB scripting on windows. London: McMillan Press. Erich, G (1996). Design patterns: Elements of reusable object oriented software: London: Addison-Wesley Publishers. Philip, T (1992). Insight into MySQL security practices. Durban: Oxford Press. Bill, L (2007). IT code of ethics. Sydney: Australian Press. Read More