Database Programming Assignment: Use of NoSQL database in Hillview Farm Box
Question
Task:
Case Scenario to be used in database programming assignment:
Use of NoSQL database
Hillview is a small business that has started up a farm box delivery business to connect farmers to customers.
The image below shows a screen shot from the Hillview Farmbox website. You can see the sort of data (that customers see) that is stored for each item. You can find out more about the Hillview Farmbox business at this link https://hillviewfarms.com.au/.
The Hillview Farm Box web site currently uses a MySQL database which was set up by one of the owner’s children. The system is functional, but it is starting to slow as the business grows at a rapid pace. The Hillview Farm Box owner knows that customers will not be happy if the web site continues to run slowly and has asked you for your advice on how it could be redesigned using MongoDB to improve speed.
The owners have provided you with the current relational schema so you can see the data that they need to store.
The current relational schema is:
Assignment Requirements:
Part A – MongoDB
- Write code to insert five product sales on the Hillveiew Farm Box web site – you will have to choose the attributes you want to store for each sale – remember this is a database rewrite. (10 marks)
- Justify your choices for the data structures you selected for each attribute in the sale (500 - 1000 words). Explain why you chose each of the data structures and justify these choices. (10 marks)
- Create the following queries (all output should be easily read) HINT use .pretty(20 marks total)
- List all the sales in the collection (including all fields) (2 marks)
- List only the product names from each sale (2 marks)
- List only the product name and farmer’s name for every sale (2 marks)
- List only the distinct name and details of every customer (2 marks)
- Count the number of sales in the collection (2 marks)
- List only the product name and price for products that are greater than $15.(2 marks)
- Return the average price each product type (eg Meat, Dairy, Veggies & Fruit)(2 marks)
- Write code that updates the name and price of one of the products in one sale. (2 marks)
- Add a new field to a sale to record to show who the sale was referred by. (2 marks)
- Return the sales that do not have any dairy products. (2 marks)
Part B – Neo4j (10 marks)
The owner has also asked you to write about how Neo4j could be integrated to improve the product recommendations. At the moment, items are randomly selected from the product list to display to customers.
Write around 300-500 words on the way that you would develop a Neo4j database that could complement the MongoDB database so that real time recommendations could be made based on what other “similar” customers purchased.
Answer
Database Programming Assignment Part A – MongoDB
1. Coding to insert five Product sales
Db.Product.insert
(
{
"Product ID": ,
"P_Number_in_stock" :,
"P_Description" : ,
"P_Weight" : ,
"P_Cost" : ,
"P_in_date" : ,
"P_Name" : ,
“P_type” : ,
}
)
2. Justification and Explanation of the Selected data Structure
As per the database schema, there is a total of five entities here, such as HillView_Customers, HillView_orders, HillView_line, HillView_farmer, and HillView_Product are entities. In the first entity that is HillView_Customers C_Postcode C_Phone number, C_Fname varchar (50), C_Lname varchar (50), C_Address, C_Phone are attributes and the primary key is Cust_id. In the next entity HillView_Orders, the primary key is Order_id and O_delivery_date date, O_order_date are the attributes. In the third entity, HillView Line Order_id, Product_id, L_Price number, L_units_added number attributes where Line_id is the primary key and Order_id, and Product_id are foreign keys. In the next entity HillView Product Product_id, Farmer_id, P_Number_in-stock number, P_description,P_weight, P_cost, P_in_date, P_name, P_type are the attributes. Here, Product_id is the primary key and Farmer_id is the foreign key. In the last entity HillView Farmer Farmer_id, F_Fname, F_Lname, F_address, F_state, F_Post_code are the entities. Here, Farmer_id is the primary key.
The primary key is utilized for ensuring data are present in a particular column is distinct. It is identifying the record present in a relational database uniquely. The database allows just one primary key in a table. It is basically a combination of unique as well as Not Null constraints. It is not allowing any NULL values, The values of the primary key will never be deleted from the parent table. The constraints of the primary key are defined implicitly on temporary tables. On the other hand, a foreign key is a column or a specific group of columns present in a relational database table, which is providing a link among tables and data. It is referring to a particular field in the table that is the primary key to some other table. Unlike the primary key, two or more foreign keys are allowed in the database table. It may consist of duplicate values or tables in the relational database. It may also include some NULL values. The value might be removed from the child table. The constraints of the foreign key will not be defined on global or local temporary tables (Davidson and Moss, 2016).
Data modeling takes place in three levels that are conceptual, logical, and physical mode level. A conceptual model is identifying the high-level along with a user view of data. On the other hand, a logical data model comes in between the conceptual level and the physical level. It is found allowing logical data representation that is kept away from physical storage. Data models are comprised of entities that are basically some objects used for tracking data. The entities appear in the form of tables in the database. Just like HillView_Customers, HillView_orders, and more. It can be stated that entities will not be existing in isolation, however, there exists a connection among one another. The connections are better termed as relationships. There exists a one-to-one relationship among the entities. A one-to-one relationship is the most common. Entity integrity states the fact that data are reliable in case it is within a single entity or a single database table. On the other hand, another significant concept is referential integrity (Mochocki, 2020). It states that the relationship existing among the two entities is quite reliable. The utilization of foreign key is a significant step towards the referential integrity.
3. Database Queries
a.
b.
c.
d.
e.
f.
g.
h.
i.
j.
Part B – Neo4j
4. Needs of Developing Neo4j Database
Taking advantage of various database technologies will be helpful for polyglot persistence. Like when a product catalog will be using the document database then MongoDB will gain the power to search or browse their products beside graph databases like Neo4j. For enabling this polyglot persistence the application will require several databases for storing valuable data. For connecting MongoDB to Neo4j data will be synchronized automatically to make the process easier. Using Metadata Neo4j is better than MongoDB to take advantage of data relationships and Neo4j is much simpler compared to MongoDB. Neo4j doc manager project will take facilities with the help of this process.
All developers of MongoDB will provide mongo-connector which will be helpful for the project and it helps to get connected with all operations which are running in Neo4j. It will be facilitating all updates to monitor various systems. For the synchronization process, all data from MongoDB will be shifted to Neo4j with the help of the Neo4j Doc manager. It will be helping through the synchronization process of one-way from MongoDB to Neo4j. Here, these databases are intended to make compensations and strengths through the application. Neo4j doc manager also ensures that while its running, any authentication which will be made will be set to NEO4J_AUTH according to their environment. It also contains a specific name and password. While running Neo4j in the system, a user has to verify that MongoDB will be running as the facsimile set. After the process has been done mongo-shell will open and the process will be running continuously. It will be referred to Mongo connector FAQ to collect more information. MongoDB connecting service will be running by following some commands like –m to provide the endpoint of MongoDB, -t for specifying all Neo4j endpoint, -d to verify that Neo4j Doc manager will act as the main doc manager.
For viewing all these configuration operations, the Neo4j doc manager will be including these specific collections and also some fields for the synchronization process. Data synchronization will be done with the help of Neo4j-manager while all services are running in the system and if any document will be inserted into MongoDB then it will be turned into a proper graph structure and after that, it will proceed into Neo4j as well. All these document keys will be turning into new nodes. Nested values are capable of turning into minor properties. To view more advantages Neo4j Doc manager will be providing some graph structures. Relational database and other datasets like MongoDB will be used for simplifying all data storage. By spreading and dividing al data, the user will be able to perform more retrieval and some updates will be helping to make them more user-friendly (Bo?ek, K., 2017). To build any software Neo4j will work much better than MongoDB and other hierarchical models. While starting data modeling, at first user need to identify all domains which will be pointing the database. In this case, Neo4j will be easier to use.
Reference list
Bo?ek, K., 2017. Neo4j vs MongoDB.
Davidson, L. and Moss, J., 2016. Pro SQL Server Relational Database Design and Implementation. Database programming assignment Apress.
Mochocki, S.A., 2020. Relational database design and multi-objective database queries for position navigation and timing data.
