Report: This report is on database design and implementation
Here in this application we have a table which gives the information about the course details, course work numbers due date, number of hours to complete the course and learning out comes also. So that we can easily understand what is there further in that course.
Then the Plagiarism report and course work submission requirements regulations also mentioned here. Some specifications are also given here.
This application is based on library system. The specifications can give the information about library system. In this the library is going to used by students and staff member. And the students from out campus can use the library and the library having journals, electronic books, video and audio collections. And the library needs to maintain the list of borrowers and number of copies of books.
Library need to maintain the Author names, Titles, Key word, category and year of publication. Library staff is divided into librarian, school associate librarian, reference librarians. Books can be cheeked out and need to maintain the reports. Priority can be give to the borrowers.
Applications:
To create the database in SQL serve we need to install SQL serve. In that page in object explorer we need to create new database for library system.
Syntax:
CREATE DATABASE database _name
To create a table in database the syntax is give as CREATE TABLE table _name( colum_name1 data_type,column_name2,….)
For A1: CREATE TABLE barrower (barrower _name char, barrower _id NOT NULL int, barrowed _item varchar)
Here we used the constraint NOT NULL. So that the Colum name barrowed _id value won’t be not null. There should be some value and that should be UNIQUE to avoid the replication in data base.
We can create that table using PRIMARY KEY also. It uniquely identifies the records in database, and one table should have one primary key. If we want to have PRIMARY KEY on multiple columns we have different syntax.
Example:
CREATE TABLE barrower (barrower _name varchar NOT NULL, barrower _id int NOT NULL, barrowed _item int, CONSTRAINT PK _barrower _id PRIMARY KEY (barrower _name, barrower _id)
For A2: CREATE TABLE barrowed _item(item _name char, item _id int, barrowed _date int)
Here we will get the table barrowed item. The columns are item name, item id, and barrowed date. The date format can be given. In SQL the supporting formats are
DATE – format YYYY-MM-DD
DATETIME – format: YYYY-MM-DD HH:MM:SS
SMALLDATETIME – format: YYYY-MM-DD HH:MM:SS
For A3: CREATE TADLE categories (category _name varchar, category _number int)
Example:
CREATE TABLE categories (short lone char, ordinary lone varchar , reference item varchar )
The table will give the information about the short lone, ordinary lone and reference items.
For A4: CREATE TABLE title(title _name varchar, published year int, language _name char, binding _type varchar)
ForA5: SELECT *from barrower where barrower _id=bw _id;
SELECT * from barrowed _item where barrowed _date=bw _dt;
Here we need to link the two tables barrower and barrower _item. The required book is there in the barrower table and the date of each barrowing is given by the table barrowing _item.
ForA6:CREATE TABLE book _details(title varchar, genus varchr, length _minutes int,date _production int, publisher _name varchar)