Get 20% Off + $20 Signup Bonus ! Limited Time, Hurry !
Get 20% Off + $20 Signup Bonus ! Limited Time, Hurry !
Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!
loader
Add File

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Stuck on Your Question?

Get 24x7 live help from our Top Tutors. All subjects covered.

loader
250 words

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Students Who Viewed This Also Studied

5 Pages
COIT20247 Database Design and Development

Objectives  This assessment item is to develop a conceptual data model based on the following case study. It relates to the unit learning outcome numbers 1 and 3 in the unit profile.  Assess ...

Type

Programming

Subject

Database

3 Pages
Cashiering Function

1.Present a Context and Level 0 (logical) DFD based on the description above . If you have to assume things about the process to provide the optimal diagram, please note your assumptions below your di ...

Type

Assignment

Subject

Database

51 Pages
Volunteering Database Management System

1.4.1 Volunteering Database Management System Implementation For this assignment you will be expected to use tables’ structure given to you, as a result of feedback on Phase Test tasks. This fee ...

Type

Programming

Subject

Database

0 Page
Learning Device Management

...

Course

BCO2041

Subject

Database

University

Victoria University

Season

Fall

CIS2002 Database Design and Implementation

Question

Answered

Question:

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.
  1. 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.
  1. 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.
  1. 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.
  1. 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.
  1. 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?
  1. 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.
  1. 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.
  1. 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.
  1. 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.
  1. 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.

CIS2002 Database Design and Implementation

Answer in Detail

Solved by qualified expert

Get Access to This Answer

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

48 More Pages to Come in This Document. Get access to the complete answer.

MyAssignmenthelp.com boasts a team of talented and experienced thesis helper who are capable of providing best quality thesis help services. Our wide range of thesis writing services include assistance with writing and structuring top-quality thesis, help with research, topic selection help and editing and proofreading help. Students can get any of these services at cheap price. Despite charging cheap from student, our thesis writers never compromise with the quality. They efficiently answer students queries pertaining to can someone help me with my thesis paper.

More CIS2002 CIS2002 Database Design and Implementation: Questions & Answers

Q

COIT20247 Database Design and Development

Objectives  This assessment item is to develop a conceptual data model based on the following case study. It relates to the unit learning outcome numbers 1 and 3 in the unit profile.  Assessment task   In this assignment, you are going to perform the entity-relationship data mode ...

View Answer
Q

Cashiering Function

1.Present a Context and Level 0 (logical) DFD based on the description above . If you have to assume things about the process to provide the optimal diagram, please note your assumptions below your diagram. 2.Present a Process Map based on the description above . If you have to assume things about ...

View Answer
Q

Volunteering Database Management System

1.4.1 Volunteering Database Management System Implementation For this assignment you will be expected to use tables’ structure given to you, as a result of feedback on Phase Test tasks. This feedback will be available on the VLE, by week 7. Please read carefully the marking schema when doing y ...

View Answer
Q
icon

We aren't endorsed by this University

Learning Device Management

...

View Answer

Content Removal Request

If you are the original writer of this content and no longer wish to have your work published on Myassignmenthelp.com then please raise the content removal request.

Choose Our Best Expert to Help You

5% Cashback

On APP - grab it while it lasts!

Download app now (or) Scan the QR code

*Offer eligible for first 3 orders ordered through app!

screener
ribbon
callback request mobile
Have any Query?