Data Modelling and Database Design

Reflection of Excel Table

ABC library is the small community library and it is situated in the northern suburbs of Adelaide. In some years, the collection of books and the number of borrowers increased at a very high rate so it became difficult for the library staff to handle this data manually, now the library members want to enhance its services and want to implement a computerised library management system.

There are four versions of table in the ABC library that the library staff has to maintain. This system can be implemented with the help of Relation database management system. RDBMS is the basis of all modern database system such as SQL, MS SQL server, MYSQL, IBM DB2 and MS Access. RDBMS is a basis of all database system and it is introduced by E. F. Codd. There are terminologies such as table, row or record, column, NULL value, SQL constraints and Data integrity, Database normalization, and various others. In RDBMS, relation is called table.

All the data in RDBMS is stored in the database objects and these are called tables. Table is the collection of number of rows and columns and in the rows and column, all related entries are stored in the table. Table is the simplest form to collect the table and it is the most common form to collect the data in the relation database.

Field is also known as attributes in the table. It is the different columns heading in the table, and each table is made up of different types of field.

Record is also known as row. A row is the data in the database that exist in the table. Column is the vertical entry that made in the database, and contains all the information attached with the particular field in the relation.

NULL value is the value of table in which the entry of row or column appears to be blank. In simple words, Null values means that there is no value in that specific field.

SQL constraints, there are some constraints or rules that applied in the rows or column of the table. These constraints help in limiting the type of data that entered into the table. These constraints help in making the data more reliable and more accurate. These constraints can be applied to the table either in the table level or column level. The column level constraint can eb applied to only one column, on the other hand, the table level constraint can be applied to the complete table.

There are some most commonly used and applied constraints in the database management system. These are;

NOT NULL constraint: it makes sure that the column should not have the Null value.

UNIQUE constraint: It makes sure that every value in the column is different.

PRIMARY key: It helps in identifying the table with the help of unique way. For example, there is table named student and this table has different attributes such as Student Id, name, class, and many more. Here name can be repeated and class can be repeated but the student Id is unique in the table. This is called primary key.

FOREIGN key: It helps in identifying the record or row in any other database relation.

CHECK constraint: This helps in making sure that all the values in the column satisfy every condition.

INDEX: it helps in creating the data and retrieving the data from the database.

Default constraint: It offers a default value to the database if there is no vale is specified.

All these constraints and everything which is stated above used in the ABC library.

In V1.0, the attributes of the table are, S.no, Author name, book title, genre, issue date, return date, borrowers, and library staff.

These are the attributes, so it is necessary to insert the value in all the attributes and columns. Value can be inserted into the database with the help of queries.

INSERT into ABC library (S.no, Author name, book title, genre, issue date, return date, borrowers, library staff)

VALUES (1, Jane Austen, Pride and prejudice, Fiction, 23-03-2020, 25-03-2020, Elon, Library Aide), (2, Walter Isaacson, Steve Jobs, Biography , 22-05-2020, 25-06-2020, Musk, Library Aide),(3 , Charles C. Mann, 1491: New Revelations of the Americas Before Columbus, History, 08-09-2020, 08-10-2020, Allen, Library Aide), (4, Arthur conen Doyle, Sherlock Holmes, Mystery, 11-09-2020, 15-09-2020, Mac, Library Aide), (5, Arthur Winter, Organic Chemistry, Chemistry, 03-09-2020, 06-09-2020, Kavin, Library Aide), (6, Joanne Rodrigues, Demography is Politic, sociology, 12-09-2020, 15-09-2020, Sam, Library Clerk), (7, Robert Kanigel, The man who knew Infinity mathematics, 18-09-2020, 20-09-2020, Bianca, Library Clerk), (8, Eugene O'Neill, Ah, Wilderness!, English, 22-06-2020, 15-06-2020, Tim, Library Associate), (9, Adam Alter, Irresistible, Non-fiction, 27-05-2020, 29-05-2020 , Tom, Library Associate), (10, The tangled tree, David Quammen, Biology, 09-09-2020, 19-09-2020, Ronaldo, Library Clerk)

In V1.1,

UPDATE ABC library

SET Author name =’ Alex Michaelides’, Book title=’ The silent patient’, genre=’ Thriller’, issue date=’ 27-05-2020’, return date=’ 29-05-2020’and Author name =’ Tracy k Smith’, Book title=’ The good life’, genre=’ poetry’, issue date=’ 09-09-2020’, return date=’ 19-09-2020’.

WHERE S.no = 9 and 10.

Insert into ABC library (S.no, Author name, book title, genre, issue date, return date, borrowers, and library staff)

VALUES (11, Adam Alter Irresistible, Non-fiction, 27-05-2020, 29-05-2020, Tom, Library Associate),

(12, the tangled tree, David Quammen, Biology, 09-09-2020, 19-09-2020, Ronaldo, and Library Clerk),

(13, Tommy Koh & Li Lin Chang, 50 Years of ASEAN and Singapore, social science, 22-05-2020, 25-06-2020, zoo, Library Clerk),

(14, Robert Brian, Java, computers, 03-09-2020, 06-09-2020, Joe, and Library Associate),

(15, Mishra and puri, macroeconomics, economics, 11-09-2020, 15-09-2020, Peas, Library Associate)

In V1.2,

ALTER ABC library

ADD ISDN and Borrower ID

This is for adding the attributes into the table and to insert data in each attribute, there is another query,

INSERT into ABC library (ISDN) values (1234) and (Borrower ID) values (11)

INSERT into ABC library (ISDN) values (3455) and (Borrower ID) values (12)

INSERT into ABC library (ISDN) values (2345) and (Borrower ID) values (13)

INSERT into ABC library (ISDN) values (3456) and (Borrower ID) values (14)

INSERT into ABC library (ISDN) values (4567) and (Borrower ID) values (15)

INSERT into ABC library (ISDN) values (5678) and (Borrower ID) values (17)

INSERT into ABC library (ISDN) values (6789) and (Borrower ID) values (18)

INSERT into ABC library (ISDN) values (7890) and (Borrower ID) values (19)

INSERT into ABC library (ISDN) values (9870) and (Borrower ID) values (20)

INSERT into ABC library (ISDN) values (7650) and (Borrower ID) values (21)

IN V1.3

ALTER table ABC customer

DROP COLUMN Library staff;

INSERT into ABC library (S.no, Author name, book title, genre, issue date, return date, borrowers, and library staff)

VALUES (11, Adam Alter Irresistible, Non-fiction, 27-05-2020, 29-05-2020, Tom, Library Associate),

(12, the tangled tree, David Quammen, Biology, 09-09-2020, 19-09-2020, Ronaldo, and Library Clerk),

(13, Tommy Koh & Li Lin Chang, 50 Years of ASEAN and Singapore, social science, 22-05-2020, 25-06-2020, zoo, Library Clerk),

(14, Robert Brian, Java, computers, 03-09-2020, 06-09-2020, Joe, and Library Associate),

(15, Mishra and puri, macroeconomics, economics, 11-09-2020, 15-09-2020, Peas, Library Associate)

Remember, at the center of any academic work, lies clarity and evidence. Should you need further assistance, do look up to our Management Assignment Help

Get It Done! Today

Applicable Time Zone is AEST [Sydney, NSW] (GMT+11)
Not Specific >5000
+
Apply Coupon code
  • 1,212,718Orders

  • 4.9/5Rating

  • 5,063Experts

Highlights

  • 21 Step Quality Check
  • 2000+ Ph.D Experts
  • Live Expert Sessions
  • Dedicated App
  • Earn while you Learn with us
  • Confidentiality Agreement
  • Money Back Guarantee
  • Customer Feedback

Just Pay for your Assignment

  • Turnitin Report

    $10.00
  • Proofreading and Editing

    $9.00Per Page
  • Consultation with Expert

    $35.00Per Hour
  • Live Session 1-on-1

    $40.00Per 30 min.
  • Quality Check

    $25.00
  • Total

    Free
  • Let's Start

Get
500 Words Free
on your assignment today

Browse across 1 Million Assignment Samples for Free

Explore MASS
Order Now

My Assignment Services- Whatsapp Tap to ChatGet instant assignment help

Collect Chat

refresh