Description
Project Description: In this project, you are supposed to implement a small library database with a simple web-based user interface. The only borrowable items are books in the library. Borrowers should be able to search books according to title, ISBN, author, etc. Users can borrow books with TC number. Insert book Delete book, Search books according various fields. Borrow books for two weeks. Each person may borrow at most 8 books. How many books a person has borrowed and their due dates should be accessable. You should have several SQL retrieval statements, use one trigger and at least one stored procedure. You have to use SQL on any relational DBMS of your choice. There should be a web based user interface. You are free to use any programming language to embed SQL and realize a web access. The user interface should be very simple. The end result is a report and a program. Due date is September 14, 2020. The report has the following format: Title page Introduction Relation definitions. Show that they are in conformity with normal forms. SQL DML Constraints Screen shots of input and output Conclusions There will be buyers, books, songs, albums, and artists in the platform. These entities will have the following properties: • Listener: Username and e-mail. They are both unique which means there exists only one listener with a specific username and e-mail address. • Artist: Name and surname. They are not necessarily unique independently but you can assume there exists only one artist with a name and surname couple. • Album: ID, genre, and title. By definition, each ID is unique. Each album must contain at least one song. • Song: ID and title. By definition, each ID is unique. Each song must reside in only one album. Each song may be produced by one or multiple artists. Two types of people will be using DBtify: Listeners and artists. You do not have to implement an authentication mechanism. You are allowed to provide two options for being a listener or an artist. The person can choose what he/she is and cannot perform other type of person’s operations. 2 Requirements Your UI must support the following operations: • Artists shall be able to add/update/delete albums. • Artists shall be able to add/update/delete songs in the albums. • Listeners shall be able to separately view all songs, albums, and artists in DBtify. • Listeners shall be able to view all songs and albums of an artist. • Listeners shall be able to view all songs of an album. • Listeners shall be able to like songs and albums. 1 • Listeners shall be able to view other listeners’ liked songs as well as his/her liked songs. • Listeners shall be able to view popular songs (according to number of likes) of an artist. • Listeners shall be able to rank all artists by the total number of likes of their songs. • Listeners shall be able to view songs of a specific genre. • Listeners shall be able to search a keyword and view the songs that contain this keyword in their titles. • Listeners shall be able to view the artists who produced a song together. This must be implemented as a stored procedure. Parameters of this procedure are the artist’s name and surname. • The system shall have three triggers: 1. When an album is deleted, all the songs in this album must also be deleted. 2. When a song is deleted, it must be removed from listeners’ likes. 3. When a listener likes an album, all the songs of this album must also be liked by that listener. 3 Notes • The quality of the web interface does not matter. So, you don’t need to style it. The functionality of the system will be evaluated. • The allowed languages are PHP, Java, JavaScript, and Python. You can use a framework, however, you must write the SQL queries and boot the database server yourself. Note that you should set up the database and create the tables on your own. You are not allowed to use any tool that helps with these parts. • You are not expected to deploy your system. So, it is fine for it to work on your local. • There is no restriction for database choice, you can use any relational databases. Non-relational databases will not be evaluated at all. 4 Report & Grading Submissions will be through Moodle. The submission must include your code (∼%80) and ER diagram(s) (∼%20) describing your system. Your diagram(s) should follow the conventions described in the course material. Otherwise, you should explain clearly what shape corresponds to which concept. The system will be evaluated during a demo session that will be arranged. Demo day(s) will be announced.