Database Design & Implementation for BTC AU

Questions: Section A (Applied Database Theory) BTC AU is the second largest Bitcoin exchange company in Australia. While it focused on keeping up with its core business in a new and emerging environment BTC AU paid scant attention to managing the rest of its rapidly expanding business operations. Integration of its business processes with IT became one of the priorities of BTC AU. The current approach to its backroom data handling management has been ad hoc and piecemeal. There was no overall map, plan, or model guided approach to the evolution of its systems. Records of past business performance have been hard to elicit from the traditional file processing systems leaving the company with an inability to gain insights to drive business planning forward. The CIO of the company Sam Nakamoto wants to look at a database approach to integrating the various applications currently running within the organization with the hope of ultimately using the data to support decision making and developing new insights for management in order to gain competitive advantage. As a business consultant to BTC AU, you are asked to develop a short brief to management, in the form of an essay (around 1000 words), highlighting the following: 1. The existing and potential problems with the current data processing approach.2. The need for a database approach to satisfy their organizational and decision making demands of the company.3. A suggested database developmental approach4. The primary activities associated with database design process. Required to extensively research on the relevant topics and present concise and workable solution. Section B (SQL) For each question, marks will be awarded for the SQL and for the correct output. The following E-R diagram represents a Car Hire database. In this question, you will use the CAR HIRE database. The CAR HIRE database including appropriate data will be made available on the USQ Oracle server. You must use this data. The table descriptions appear below, including the column names and data types. I_CAR Column Name Type and Size Constraints Description Registration VARCHAR2(7) NOT NULL Registration number of the car. This is the Primary key. Model_name VARCHAR2(8) FK Model for the car. Foreign key into the Models table. Car_group_name VARCHAR2(2) FK Group code defining type of car and rental cost. Foreign key into the CarGroups table. Date_bought DATE Date the car was purchased. Cost NUMBER(8,2) The original cost of the car. Miles_to_date NUMBER(6) The current mileage of the car as read at the end of the most recent rental. Miles_last_service NUMBER(6) The mileage of the car when it was last serviced. Status CHAR(1) The current status of the car. A for available, H for on hire, Sfor being serviced, X for in need of service or repair. Column Name Type and Size Constraints Description Car_group_name VARCHAR2(2) NOT NULL The car group code. This will be one of the following values: A1, A2, A3, A4, B1, B2, B3, or B4. This column is the primary key for this table. Rate_per_mile NUMBER(3) The charge per mile for cars in this group in cents. Rate_per_day NUMBER(5,2) The rental charge per day for cars in this group in dollars and cents. I_MODEL Column Name Type and Size Constraints Description Model_name VARCHAR2(8) NOT NULL The model name, an abbreviation of the full model name. This is the primary key for this table. Car_group_name VARCHAR2(2) FK The group to which this model of car belongs. Description VARCHAR2(30) Full description of the model. Maint_int NUMBER(5) Number of miles between services for this model. I_CUSTOMER Column Name Type and Size Constraints Description Cust_no NUMBER(5) NOT NULL The customer account number. This is the primary key for this table. Cust_name VARCHAR2(20) NOT NULL The name of the customer. Address VARCHAR2(20) Street address of the customer. Town VARCHAR2(20) Town the customer lives in. County VARCHAR2(20) County the customer lives in. Default is Australia Post_code VARCHAR2(10) Postcode for the town. Contact VARCHAR2(20) Name of person to contact. Pay_method CHAR(1) Code to indicate the usual payment method for this customer. A indicates an account, C indicates cash or credit card, NULL indicates unknown. I_BOOKING Column Name Type and Size Constraints Description Booking_no NUMBER(5) NOT NULL A serial number used to uniquely identify the booking. This is the primary key for this table. Cust_no NUMBER(5) FK Customer number of the customer making the booking. Date_reserved DATE Date on which the booking was made. Reserved_by VARCHAR2(12) Name of the person who took the reservation. Date_rent_start DATE Date on which the rental commences. Rental_period NUMBER(3) Length of rental period in days. Registration VARCHAR2(7) FK Registration of the car actually rented. Model_name VARCHAR2(8) Model of the car rented. Miles_out NUMBER(6) Miles on the odometer at the start of the rental. Miles_in NUMBER(6) Miles on the odometer at the end of the rental. Amount_due NUMBER(6,2) Cost of the rental. Calculated when the car is returned. Paid CHAR(1) Flag to indicate if this rental has been paid for. Y if it has been paid and N if not. Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT. A screen dump of the output is acceptable. Show as many rows as you can. A screen dump is usually done via the ALT + PRNT SCRN command sequence. While the output helps to understand your solution, you should not be analysing the output of the query in detail. As long as you are confident that your query corresponds to the question completely, your output may not be significant. You can have an output that says NO ROWS FOUND and it could be a perfectly valid output as long as your query fulfils the requirement. 1. Display structure of the i_model table. Display all the information stored in the i_model table belonging to car group A4.2. Display the car registration, current mileage (miles_to_date column) and purchase date (date_bought column) for all cars. Order by current mileage in descending order.3. Display all the cars registration, miles to date and status for all the cars that have not had a service but are available for hire. Order the list in ascending order of miles to date. 4. Display the booking number, date rent started, period of rental and the expected end date of each rental (i.e. date_rent_start + rental_period) and sort the output by the expected end date in descending order. Label the expected end date of each rental as: EXPECTED END DATE. 5. Display full details for all the bookings where the distance travelled during that booking (i.e. miles_in miles_out) is less than 1000 miles. 6. Display rental period, booking number, name of the person who took the reservation, the date that the rental period starts and where Paid is set to N for all the bookings where the car has not been returned. List the result by the rental period for the booking in descending order. 7. Display full details for any car that: (a) costs more than $100,000.00 or the name of the car model starts with the letter L and (b) belongs to group A4 and has a registration whose second digit is 9.8. Display full details for all car groups belonging to A1, A2, B1 or B2 AND the rate per mile is either 110 or 120. Find a way of optimising your code so that the complete SQL statement has only ONE logical operator (AND, OR, NOT).9. Display full details for all cars that have not been serviced before and have a current mileage of greater than 900. 10. Display full details for any car that: (a) costs less than $50,000.00 or the name of the car model starts with the letter D and (b) belongs to group B2 and is currently available. (You should have a single query that completely fulfils all of the above conditions).11. Display the name, town and usual payment method of all customers. Order by payment method ascending within town descending. Your query must NOT use the WHERE clause. 12. Display the booking number, registration and the name of the customer who made the booking. You may need to use traditional join between i_booking and i_customer tables to display all the required columns. Section C (Data Modelling) PART MARKING: We will only mark TWO of the four questions below but you must submit answers to all four. We will choose the questions we mark randomly and mark the same questions for all submissions. Model answers will be supply for all questions during the semester. Construct data models for the following specifications. Include an ERD and a list of relations (entity list). Your relations must show all attributes, primary keys and foreign keys. You must use the USQ (Finkelstein) methodology as described in your Study Book, the lectures and the tutorials. Question 1. A questionnaire is a research instrument consisting of a series of questions. We need to develop a model to store questionnaires and its underlying questions. A questionnaire records questionnaire name, primary contact name, start date and expected end date. A questionnaire may have many questions. Each question has a question number, question text and a category description for the question. Question 2. A mobile phone model consists of a number of variants (different styles, colours and features) which are sold as separate units. We need to develop a data model to record different mobile phone models and their variants. A mobile phone model records brand name, model name, model number (unique) and start year. A mobile phone model may have several variants. Each variant has a variant code, style, colour, feature, launch year and discontinued year. Question 3. A Bitcoin wallet manages your Bitcoins. A wallet does not store Bitcoins but holds the private keys of the owner of the wallet that allow him/her to access his/her bitcoin addresses and to sign transactions to allow the owner to spend his/her funds. Question 4. An event coordinator may be working on a number of events over time. An event can have multiple event coordinators working on it but will only have a single event manager. For an event coordinator, we store a unique coordinator identification, his/her office number and area of specialty. We also need to store the date the event coordinator commenced work on the event and the name of the event, the event managers name and other relevant event details. Prepare the following for all four questions: 1. An ER diagram: Show all entities, relationships, cardinalities and optionalities. Also, include all intersection entities but do not create entities that are not specifically covered by the specification. You must use the Finkelstein methodology as per the study book and tutorials.2. A list of relations (entity list). Produce complete relations for all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned but do not create any other additional attributes. Answers: Section A Essay on Database Development for BTC AU BTC AU is a bitcoin exchange company in Australia. This is the second largest company in the bitcoin industry in Australia. The business is growing. Till now, it used to have a file processing based system for data storage and processing. However, with the growth in the business, more data are being processed and it is becoming highly inefficient to use the same system. Other business processes of the company are also working as stand-alone systems and there are slow downs and other compatibility or efficiency issues from those systems. Thus BTC AU wants to have an efficient and planned information technology infrastructure for their business. They want to integrate all business processes, implement one database for better organization and storage of data. Further they want to have decision making support from the new information technology infrastructure. File based data processing and storage systems are no efficient enough today. Those are useful for personal use or smaller business purposes. For efficient data management and storage there are significant drawbacks of this system. It follows an ad hoc approach in data processing. The data are stored among the files scattered over the physical storage. There is no strong organization of data in those files so that searching information from data will be faster and efficient. Another significant problem with file based data processing systems is redundant data. As there will be more than one file containing same piece of data, so it is almost impossible to update all those occurrences of the same piece of data over multiple files. Thus there will be inconsistencies about the data in the system. Same operation on the data will give different results based on the source files. This is not desirable in any case. A significant amount of storage space is also wasted due to redundant data. Using file based systems, it will be very difficult to integrate different business processes, and data will be stored in different formats, forms, so there may be compatibility issues. There may be invalid data in the system also. Sharing of data from these file systems is very inefficient as it is hard to implement synchronization. Implementation of access control and other constraints are difficult to implement in traditional systems. Files can get corrupted easily so there will be security and data protection issues with file based systems. An alternative solution for BTC AU is implementation of a database across the business processes. The database will store all details and data about the business in an organized way. All core business processes will be integrated. It will reduce the data redundancy issue significantly and the database will hold all updated and accurate information. There will be no invalid or outdated data. Modifications and update of the data will be easier. Users can update data or work on those data from a single interface and just by filling some forms. Results of data analysis, queries etc. will be presented in a report format. These queries will help in finding several insights and results of business critical information like profits, losses etc. It will also help to understand different financial and sales data analysis etc. Thus database will also help BTC AU in implementation of decision making process. Implementation of security and access constraints and controls are easier with databases. It also helps to implement several legal policies like data protection laws etc. A database development can have different phases. For example, at first there will be conceptual database design then it will be mapped into relational or non-relational database. There are relational SQL, MySQL based database and advanced non-relational databases like MongoDB etc. that extends the power of SQL by integrating non-relational structures. For BTC AU, it is recommended that they take some phased approach in development and implementation of the database for their business. At first, all entities and the relationships among those entities will be identified from the business processes. Then the attributes, key attributes of those entities will be identified. Finally the conceptual model will be developed by describing each of the findings. Mapping of conceptual model into relational model will be done next. In this step, relations in the form of tables will be developed and implemented. Data will be stored into and retrieved from those tables. Those tables will be used in queries. The primary keys will ensure that there will be no redundant entry in each table. The foreign keys will tie two table and ensure better organization of information. It helps in reducing redundancy. It also ensures cascading effect while updating data. Other than that, there will be better usage of the storage. Search facilities will be enhanced in the database. Integration of the business processes will be done easily with using s central database for all processes. Then each business process will have access to updated data all the time. But the system will need to have higher degree of consistency and support for concurrency control otherwise, it will not be able to support multiple access to data. In the phased implementation approach, a part of the business will have the database implemented first, then gradually the database will take over the whole business. As, it needs to integrate all processes and there are data to be entered into the system from the old file based systems, so this phased approach has been suggested. There will be some provision for back up control also. Otherwise there may be risks of loss of data. So far, it has been described that in the current business scenario how BTC AU can implement a database for their business and can come out of the difficulties posed by the file based system on the business. A database development approach called relational database development has been outlined for the company along will description of the pros and cons, how to implement etc. References Coronel, C. Morris, S., 2014. Database Systems: Design, Implementation, Management. s.l.:Cengage Learning. Garcia-Molina, H., Ullman, J. D. Widom, J., 2011. Database Systems: The Complete Book. s.l.:Pearson. Hoffer, 2008. Modern Database Management. s.l.:Pearson. Pratt, P. Adamski, J., 2011. Concepts of Database Management. s.l.:Cengage Learning. Silberschatz, A., Korth, H. Sudarshan, S., 2010. Database System Concepts. 6th ed. s.l.:McGraw-Hill Education. Section B SQL Commands 1. DESC i_model; SELECT * FROM i_model WHERE Car_group_name = A4; 2. SELECT Registration, Miles_to_date, Date_bought FROM i_car ORDER BY Miles_to_date DESC; 3. SELECT Registration, Miles_to_date, Status FROM i_car WHERE Status = A AND Miles_last_service = NULL ORDER BY Miles_to_date ASC; 4. SELECT Booking_no, Date_rent_start, Rental period, Date_rent_start + Rental period AS Expected_end_date FROM i_booking ORDER BY expected_end_date DESC; 5. SELECT * FROM i_booking WHERE DATEDIFF (Day, miles_in, miles_out) 1000 ; 6. SELECT Rental_period, Booking_no, Reserved_by, Date_rent_start FROM i_booking WHERE Amount_due = 0.00 AND Paid = N ORDER BY Rental_period DESC; 7. SELECT * FROM i_car WHERE (Cost 100000.00 OR Model_name LIKE L%) AND (Car_group_name = A4 AND Registration LIKE _9%); 8. SELECT * FROM i_cargroup WHERE Car_group_name IN (A1, A2, B1, B2) AND Rate_per_mile IN (110, 120); 9. SELECT * FROM i_car WHERE Miles_last_service = NULL AND Miles_to_date 900; 10. SELECT * FROM i_car WHERE (Cost 50000.00 OR Model_name LIKE D%) AND (Car_group_name = B2 AND Status = A); 11. SELECT Cust_name, Town, Pay_method FROM i_customer ORDER BY Town DESC, Pay_method ASC; 12. SELECT Booking_no, Registration, Cust_name FROM i_customer, i_booking WHERE i_customer.cust_no = i_booking.cust_no; Section C 1. The relations are, QUESTIONNAIRE (QuestionnaireCode, Name, PrimaryContactName, StartDate, ExpectedEndDate). QUESTION (QuestionNumber, QuestionText, Category, QuestionnaireCode) 2. The relations are, MOBILE_MODEL (MobileNumber, NameBrand, ModelName, StartYear) MOBILE_VARIANT (MobileVariantCode, Style, Color, Features, LaunchYear, DiscontinuedYear, ModelNumber) 3. The relations are, WALLET (WalletID ) BITCOIN (BitcoinNumber, BitCoinAddress,, WalletID) TRANSACTION (TransactionCode, Fund, TransferType, WalletID) 4. The relations are, EVENT (EventICode, Name, StartDate, EndDate, EventManagerName) EVENT_COORDINATOR (EventCoordinatorCode, Name, OfficeContactNumber, Specialty) EVENT_EVENT_COORDINATOR (EventCode, EventCoordinatorCode, CommenceDate, FinishDate)