Section A:

You are required to write an opinion article (also known as an “op-ed” piece or article) for a technology magazine on the following topic:

Centralized and Distributed Databases : The curse of emerging technologies like blockchain.

Database technology is constantly changing the landscape of how data is stored and transacted in real time, with efficiency as the objective goal. Over the course of time, many initiatives have contemplated this fine balance of data utility and privacy with debates over the use of centralized (e.g. DBMS) or decentralized (e.g. File-based approach) databases for their information transaction systems.

As an emerging technology, blockchain is one such example which seeks to create a decentralized database environment where there is no central control. Yet however, information in this data model is shared objectively between users based on their level of trust.

The concept of the Relational Database was developed in 1970s (described by Edger F. Codd and popularized C. J. Date). The relational databases and SQL were developed when data was assumed to be neat, structured and static.

However, blockchain is formed from a chain of blocks and jointly maintained by participating nodes. Blocks are basically containers that aggregate big data which are oftentimes, complex, unstructured, disorganized and non-unique (i.e. sometimes duplicated).

The original data model which blockchain proposes was used to tackle challenges of centralized database models as single points of failure during periods when rampant information hacking was prevalent (e.g. the 414’s, Anonymous, Chaos Computer Club, Cicada 3301, Croatian Revolution Hackers, etc.).

Write an essay in your own words, describing some of the perceived and practical strengths and weaknesses between a decentralized database model (i.e. blockchain) and a centralized database model (i.e. DBMS). In the essay list and describe at least four (4) possible relational database model alternatives to blockchain. For each alternative, you need to discuss some key advantages and disadvantages of centralized to decentralized data models in comparison with blockchain.

Cite any sources of information in the enumerated Harvard APA / Chicargo format, you have used to support your claims. you are expected to provide a list of the references that you researched while writing this article. Please review the marking criteria for Section A.

Section B

For each question, three marks will be awarded for the SQL and one mark for the correct output.

The following E-R diagram represents the JustLee Books database. The script for the table creation is located on the Moodle website. Please ensure that you re-run the script to reset the tables to their default state before starting your assignment.

In this question, you will use the JustLee books database. The JustLee database including appropriate data will be made available on the USQ Oracle server. You must use this data.

Full description of the JustLee database is found in Appendix A in the handbook (Casteel) on page 539. You may also get most of the details by querying the data dictionary on the oracle server.

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 or use the ‘Snipping Tool’ under windows to capture parts of the screen.

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. Explain why the query may not contain any output in this instance.

Questions

1. Write an SQL query that displays the ISBN, book title, publication date and category. Order the list by category.

2. Amon is running the logistics operations of JustLee Books bookstore which assigns dispatch resources for delivering orders to different states across the country. His dispatch operations is based on region codes. Write an SQL query that displays the order number, the book title (rename this to DESCRIPTION), quantity, order date, zip code and region.

3. JustLee Books bookstore would like to reward their referring customers with gift vouchers for any new purchases from first time patrons. Write an SQL query to display the full name of all the referee customers in a single column (by concatenating first name followed by a comma then a space then last name), rename the field ‘Customers’, their address, zip code and email.

4. List the customer number, first name, last name, order number and order date for all orders which have not been shipped. Order the resulting set by the order date in ascending order.

5. Write a SQL query that displays customer number, last name, first name, and city for all customers who reside in the south eastern states and who have not been referred.

6. The sales and marketing department of the JustLee Books bookstore would like to understand the buying patterns of their books by some of their customers. Write an SQL script to display the customers’ full names, book title, quantity purchased and date purchased. Which book(s) appears to be most popular among customers?

7. The sales and marketing department of the JustLee Books bookstore would like to understand the buying preferences of their books by some of their customers; so that they can recommend similar genres of emerging book titles appropriately. Write an SQL script to display the customers’ full names, ISBN, Book title and category of books purchased. List the result by customer’s names so that it becomes easy to track the preference of these individuals.

8. A new batch of book releases have just been purchased by JustLee Books bookstore. The advertisers are eager to get down to pushing sales of these books as soon as possible through email blasting. These books come from a variety of genres that fit into the stock list of what the JustLee Books bookstore is currently carrying (i.e. more of the same). However, their customers may not know that these “new” books are currently available at JustLee Books bookstore. Help them write an SQL script to display the customer’s full names, the book categories from which they have recently purchased and their emails in a table. Order your table by book categories so referencing becomes easier. If customers do not have an email, advertisement flyers will be sent to their postal addresses.

9. The sales team would like to know if they are able to meet their profit margin quota for book sales in their most recent months of March and April. Display publisher name, book title and retail price for only discounted books, after subtracting their available discounts. Name the new field ‘SELLING PRICE’ and order by publisher name ascending and discounted price of the book in descending order.

10. It has been decided that old books in stock which are selling below acceptable profit margins will have to get replaced by new releases asap, so that JustLee Books bookstore is kept up to date with both reading material and running costs. List ISBN, title, retail costs, category and published date of all the books that are selling for lesser than 40 dollars and where (cost of the books is more than 20 dollars and where the published date is before the end of the financial year for 2005/2006 – i.e. 30 June 2005).

Section C

In this section, you will construct data models per the given specifications. For each of the following questions, you will need to provide an ERD and a list of relations (entity list).

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.

Question 1

An organization is made up of multiple departments, each department having a name, identifying number, and an employee who is a manager. A department may be located in different places. Information about employee includes name, identification number, birth date, address, sex and salary. Each employee is assigned one department. Employees may be directly supervised by another employee. Each project within the organization is controlled by a department. Employees (not necessarily from the controlling department) are assigned to projects. Information about projects include project name, number and location.

Question 2

A hospital contains a number of wards. Information about wards include name, total number of admitted patients, date and time. Patients may be admitted to hospitals based on external doctor recommendations, emergency or by medical consultants of the hospital. Patient details include name, age, phone number, address and a medical ID. Each patient is assigned to one acting specialist in charge from the hospital’s doctors and may be assigned tests during the length of their admission based on their medical condition. Each medical test records the time, date, unique test ID, category and final results of the test. The tests may be examined by their specialists in charge or by other doctors if required.

Question 3 

The University Housing Office receives many applications from students requesting an apartment on campus. The housing villages are sited in five different locations and each village has about 500 apartments. Each apartment falls into one of the different apartment categories of a single unit, a double unit or a studio unit and offer tenants with either a furnished or an unfurnished accommodation. The housing office keeps information about the names, telephone number, marital status, occupants and affiliated college and department of the current residents which they are enrolled in. Rent collected every week is kept by the rentals office and tracked by the agreements residents have signed at the time of moving into their accommodation. Residents have the option of paying for only rent or a combination of rent and either one or all of the three options: water, electricity and internet. A receipt will be emailed to residents for weekly rent which has been paid up to date. Reminders will be emailed to residents for late or overdue rent arrears.

Question 4

A truck driver may be assigned to many teams over time. A team comprises of a minimum of one driver but could have up to four. For driver, we store the name, license number and birth date. For team, we store the team identifier and base location. We also need to store the date a driver is assigned to a team, the date a driver leaves the team and the total kilometers the driver has driven within the team.