ISYS1055 Database Concepts-The SQL Statements

Author: 3NF

Publisher: 3NF

WrittenBy: 3NF

Book: 3NF

StokedAt: 2NF

ShoppingCart: 2NF

Customer: 2NF

The tables can be decomposed as the following:

Author (Email, Name, Address, Telephone1, Telephone2, Telephone3)

Publisher (Name, Address, URL, ABN)

WrittenBy (Email*, ISBN*, Title*)

Book (ISBN, Title, Edition, Year, ListPrice, PublisherName*)

Warehouse (Code, Address)

StokedAt (ISBN*, StockQty)

CodeStoked (Code*, StockQty)

ShoppingCart (CartID, TimeStamp)

ISBNcart (ISBN, BuyPrice, Qty)

Customer (Email, Name, Address)

CartID(CartID*,Name, Address)

The final relational database looks like the following:

Author (Email, Name, Address, Telephone1, Telephone2, Telephone3)

Publisher (Name, Address, URL, ABN)

WrittenBy (Email*, ISBN*, Title*)

Book (ISBN, Title, Edition, Year, ListPrice, PublisherName*)

Warehouse (Code, Address)

CodeStoked (Code*, StockQty)

ShoppingCart (CartID, TimeStamp)

ISBNcart (ISBN, BuyPrice, Qty)

CartID(CartID*,Name, Address)

1. SELECT TITLE FROM BOOK LEFT OUTER JOIN BOOK_COPY

ON BOOK.BOOKDESCID = BOOK_COPY.BOOKID NOT IN (SELECT BOOKID FROM BOOK_COPY LEFT OUTER JOIN

BORROW_COPY ON BORROW_COPY.BOOKID = BOOK_COPY.BOOKID);

1. SELECT TITLE FROM BOOK WHERE BOOK.BOOKDESCID = BOOK_COPY.BOOKID NOT IN (SELECT BOOKID FROM BOOK_COPY WHERE BORROW_COPY.BOOKID = BOOK_COPY.BOOKID)

SELECT PUBLISHERFULLNAME FROM PUBLISHER WHERE PUBLISHERID=(SELECT PUBLISHERID FROM PUBLISHED_BY WHERE BOOKDESCID=(SELECT BOOKDESCID FROM WRITTEN_BY WHERE AUTHORID=(SELECT AUTHORID FROM AUTHOR WHERE LASTNAME='AHO')));

SELECT FIRSTNAME,LASTNAME FROM AUTHOR WHERE AUTHORID=(SELECT AUTHORID FROM WRITTEN_BY WHERE BOOKDESCID=(SELECT BOOKDESCID FROM PUBLISHED_BY WHERE PUBLISHERID=(SELECT PUBLISHERID FROM PUBLISHER WHERE PUBLISHERFULLNAME='MC GRAW-HILL')))

SELECT FIRSTNAME,LASTNAME FROM AUTHOR WHERE AUTHORID=(SELECT AUTHORID FROM WRITTEN_BY WHERE (SELECT COUNT(BOOKDESID) FROM WRITTEN_BY)='3')

SELECT TITLE FROM BOOK WHERE BOOKDESCID=(SELECT BOOKDESCID FROM BOOK_COPY WHERE (SELECT MAX(SELECT COUNT(BOOKID) FROM BOOK_COPY)) FROM BOOK_COPY);

In addition to the transaction filed there can be a return field and the return date attribute can be shifted to the return field.A current holder table should be introduced in which the PersonID and the name of the BookID should be provided.

(a) Loan Extensions would not be possible in this database as it is no scope for the financial aspect of the database and hence the financial instances of the database should be added.

(b) There are no scope in the database to have these type of extensions. There are no constrains in the database that can count the number of times the person is trying to avail the loan option and this constrain cannot be added to the database.

The memory is one of the most important part of the computer system. The memory management is also very important for the computer systems. In addition to this, the memory management can be easily done by the databases. Hence, the database one of the most important part of the computer system. In addition to this, for any kind of system it is impossible to contruct without proper database system for the management of the data.

The report provides the information about the different types of database and also the one that is suitable for the development of Facebook.

The report contains the Database management systems information and the type of database management systems that are prevalent in the systems right now. In addition to this, the popular database management systems are also discussed in the report.

In the present situation, the Facebook Application is to be develop from the scratch. And it is to be decided that the traditional database is to be used or the no-SQL database ids to be used. In addition to this the advantage and the disadvantage of both the option are to be discussed in the report. Therefore it is very necessary to have knowledge about both the processes are very necessary and also the different models are also to be discussed in this report.

Database Management System

The database system contains several type of software and tools that are incorporated with each other. These tools would be very useful in handling the large amount data that are flowing through the network or the system. The application provide assistance in management of the data and also helps in updating the transaction procedures. The tools come in various types of sizes and various types of shapes with various type of functions. This applications would be incorporated together in a single database solution. In addition to this for the database management systems there are different type of database models.

Database Models

Each and every database is designed with different type of logic and hence there are different type of database with different types of logic. The different types of the database model are:

The Relational Database Model:

The Relational Database model is the most popular type of database model. The relational database is both very powerful and flexible.  This model was introduce in the 1980s. The model provides a very mathematical structure of adapting to the database and also provides a very flat model for the database design. These databases store the data in a very structured format. I addition to this, the database relates all the inputs by assigning the values to the attributes.

In addition to this, the relational model are very reliable and very efficient. The database model is also very primitive type model and hence, the users are already experienced of this type of databases. Hence, the handling of this type of database are very easy for the users. But this system has many type of side effects the main factors are the glitches that occur in the data storage system and also the lost and the error in the data that occur due to these glitches. Although the relational database are very effective.

The No-SQL Database Model:

The No-SQL Database model has gained popularity lately. The database model is concerned with getting rid of all the type of constrains that are involved with the complexities of the data and that involve using the SQL queries. In addition to this, the limitation and the restrictions of the normal database models are removed in this database model. This type of database model uses the unstructured format of the data storage in the systems. The model is known as the model less data approach. The main aim of this model is to remove the strictness and the constrain in the database and make the data storage more and more efficient. But the data handling is not of the same level as that of the relational database.

In addition to this there are various kind of database models. Although there are various type of database models but the two most efficient type of the database models are the relational database management system and the No-SQL database management system.

Relational Database Management System:

The relational database management system are very popular choice of storing data and are very safe and reliable. The data that are stored in this database can be stored according the wish and the requirements of the users. The users can define the type of data they want to enter and also can be constrain for storing the data can be easily defined by the users. The data have a structured representation in this format. The data are stored in a tabular format. The data are represented in the rows and the columns. The format of the table and the database can also be defined by the users. Some important relational database management systems are:

SQLite

MySQL

PostgreSQL

NoSQL Database System:

The NoSQL Database do not have any kind of structure. The data in the NoSQL Database System are stored in the unstructured format. The database are schema less solutions. They allow unlimited number of entities. In addition to this the databases can be vein addition to this large groups of data can be stored in the database. The data in the table can be stored as the single objects. The database do not have a common way for retrieving the data using the queries. They do not make use of the traditional SQL queries. Each solution of the queries in the NoSQL Database provide their own query systems.

The NoSQL Database Systems are the following:

MongoDB

JSON

The advantage and the disadvantage of the Relational and the NoSQL Database Management System. 

The advantage of the Relational Database Management System are:

• The database provides a structured format for the storage of the data.
• The data storage in the database are very systematic.
• The database are very reliable and more secure.
• The database have a fixed size of storage.
• In addition to this the database are the popular choice for the storage of data.

The main disadvantages of the relational database management system are:

• The database are not very flexible.
• The database requires structured query languages.
• The query for the data are very complex.

 The main advantage of the NoSQL database systems are:

• The database is very flexible.
• The data storage capacity of the database can be expanded very easily.
• The queries of the databases have separate solution of their own.
• The queries of the databases are also very less complex.

The main disadvantage of the NoSQL database management System are:

• The data cannot be stored in structured format.
• The tables and the entities and the attributes that are stored in the database cannot be done manually by the users.
• The database has its own default settings and the language is new hence, the users are not very accustomed to the database and its operations.

Recommendation

The following recommendations can be provided for the implementation of the Facebook from the scratch:

Structure and the type of data: The relational database provides a structured type of system for data storage but the NoSQL databases allow the operations to be free flowing.

Query: The relational database can be queried using the SQL Queries. But the NoSQL databases cannot be queried.

Scalability: Both the databases are scalable but the NoSQL Database provides more scalability.

Reliability: The Relational database is more reliable than the NoSQL database.

Support: the relation databases would be providing a greater amount of support than that of the recently popular NoSQL Database.

Complex data needs: The relation database are more effective in the storage procedure of the complex data than the NoSQL databases.

Hence, keeping all these factors in mind the database that is to be designed for implementation of the website is the relation database model. Although the database model is a primitive type and uses the older methods yet the database is more reliable on provides efficient storage of data.

Conclusion

  For conclusion it can be said that the report has provided the discussions about the different types of the database models. The most important database that are identified in this report are the Relational database model and the NoSQL Database management model. The different type of relational database model and the NoSQL Database model are also provided in this report and in addition to this, the advantages and the disadvantages of the two models are also provided and the comparison and the suggestion about the model that is better suited for the situation is provided in this report. It has been decided that the relational database model is the better suited for the situation.

Bibliography

Eswaran, K.P., Gray, J.N., Lorie, R.A. and Traiger, I.L., 2016. The notions of consistency and predicate locks in a database system. Communications of the ACM, 19(11), pp.624-633.

Silberschatz, A., Korth, H.F. and Sudarshan, S., 2017. Database system concepts (Vol. 4). New York: McGraw-Hill.

Atkinson, M.P., Bancilhon, F., DeWitt, D.J., Dittrich, K.R., Maier, D. and Zdonik, S.B., 1989, December. The Object-Oriented Database System Manifesto. In DOOD (Vol. 89, pp. 40-57).

Tatarinov, I., Viglas, S.D., Beyer, K., Shanmugasundaram, J., Shekita, E. and Zhang, C., 2012, June. Storing and querying ordered XML using a relational database system. In Proceedings of the 2002 ACM SIGMOD international conference on Management of data (pp. 204-215). ACM.

Allen, F.H., Davies, J.E., Galloy, J.J., Johnson, O., Kennard, O., Macrae, C.F., Mitchell, E.M., Mitchell, G.F., Smith, J.M. and Watson, D.G., 2013. The development of versions 3 and 4 of the Cambridge Structural Database System. Journal of Chemical Information and Computer Sciences, 31(2), pp.187-204.

Garcia-Molina, H., Ullman, J.D. and Widom, J., 2013. Database system implementation (Vol. 654). Upper Saddle River, NJ:: Prentice Hall.

Lamb, C., Landis, G., Orenstein, J. and Weinreb, D., 2013. The ObjectStore database system. Communications of the ACM, 34(10), pp.50-63.

Dietrich, B. and Grust, T., 2015, May. A SQL Debugger Built from Spare Parts: Turning a SQL: 1999 Database System into Its Own Debugger. In Proceedings of the 2015 ACM SIGMOD International Conference on Management of Data (pp. 865-870). ACM.

Kyrola, A. and Guestrin, C., 2014. GraphChi-DB: Simple Design for a Scalable Graph Database System--on Just a PC. arXiv preprint arXiv:1403.0701.

Chu, S., Balazinska, M. and Suciu, D., 2015, May. From theory to practice: Efficient join query evaluation in a parallel database system. In Proceedings of the 2015 ACM SIGMOD International Conference on Management of Data (pp. 63-78). ACM.

Henderson, S. and Bruno, I.J., 2013, April. Cambridge Structural Database: Moving with the times. In Abstr. Pap. Am. Chem. Soc (Vol. 245).