[DATABASE]Auto incremental on primary key

I created a relation ENTRY_TEST, the primary key is entryid which will be auto incremented, when inserting data I do not need to assign ID.
Screenshot 2018-12-01 at 7.05.22 PM.png

I will insert a few values:
Screenshot 2018-12-01 at 7.09.16 PM
Screenshot 2018-12-01 at 7.09.59 PM
Screenshot 2018-12-01 at 7.10.28 PM.png

Let’s check the relation:
Screenshot 2018-12-01 at 7.11.10 PM

Advertisements
Posted in General stuffs | Tagged , | Leave a comment

[database]Functions provided by DBMS

Here is a list of non-exhaustive functions provided by DBMS (Database management system)

  • To prevent data lost during system failure, it should provide reliable recovery service.
  • Provide transaction service for user such as read (select), update, delete, commit, insert, rollback.
  • Provide authorisation service such as permission for user to insert (write new data), update (update existing data), read (select), delete on a database table.
  • User accessible catalogue such as the use of views, which provide what user is required to read.
  • Concurrent transaction protocol to prevent lost updates during transaction example of such protocol is two phase locking (TPL).
  • Provide storage, retrieval and update services.
Posted in Database | Tagged | Leave a comment

[DATABASE] File base vs database

This post illustrates the need for database approach to manage data.

Problems with file base approach

  • Data is stored in file defined by application, hence there is a possibility of potential useful data needed by another application but not aware by the application that requires it.
  • Because the data is stored in file defined by application, the structure is hard to maintain, to unify the structure you need to change on every application.
  • Because common data is not aware among applications, duplicated (redundant) data may be present in every applications.

Motivation to use database

  • Data is stored in a single location which can be accessed by any applications that need it, in other words the same data can be accessed by any applications.
  • Duplicated data can be normalised within database.
  • Data stored in database can be unified by data dictionary which defines the structure, data type and domain of data.
  • The draw back of database is its complexity in building it, expertise in database product is required.
Posted in Database | Tagged | Leave a comment

[DATABASE]ACID property of DBMS

  • Atomicity refers to a transaction is either fully completed or not completed at all, there is no partial complete transaction. This also refers to all or none rule.
  • Consistency refers during transaction the integrity constraints are maintained so that the data are consistent before and after transactions.
  • Isolation refers to changes of one transaction are not visible to another transactions until the changes are committed, this property ensures transaction can be carried out concurrently without impacting the consistency of the data.
  • Durability refers to data before and after transaction is permanently stored in non-volatile memory, data is still persist despite system failure.
Posted in Database | Tagged , | Leave a comment

[DATABASE] VIEWS

  • Views are virtual tables derived from columns and rows from the base table.
  • Views provide what data are needed by the users, saving the trouble for users to understand the complexity and abstraction of the base tables.
  • Views are dynamic, updates in base tables will reflect on views.
  • Due to views only have limited access to columns and rows defined for users, update to views do not entirely reflect on base tables

Consider the following example, RENTAL_CUSTOMER relation contains custno, name and phone number, however user who is smith only requires to know his contents, we shall define a view for Smith:
Original base table:
Screenshot 2018-12-01 at 12.01.45 AM

Screenshot 2018-12-01 at 2.31.10 AM.png

This is what Smith will see from smith_view view:
Screenshot 2018-12-01 at 2.31.55 AM

Grant read and update permission to smith:
GRANT SELECT,UPDATE ON SMITH_VIEW TO SMITH;

Posted in Database | Tagged | Leave a comment

[DATABASE]SQL query examples

There are three relations used for this example – RENTAL_CUSTOMER, CAR, RENTAL.
Below are the SQL statements used to create the relations.
Screenshot 2018-11-30 at 10.55.29 PM
Screenshot 2018-11-30 at 11.02.36 PM.png
Screenshot 2018-11-30 at 11.13.20 PM.png

Data have been inserted to the relations:
RENTAL_CUSTOMER
Screenshot 2018-11-30 at 11.20.54 PM

CAR
Screenshot 2018-11-30 at 11.22.21 PM.png

RENTAL
Screenshot 2018-11-30 at 11.22.57 PM

List all cars rented by Smith since 1st of August 2017
Screenshot 2018-11-30 at 11.30.30 PM

List each make of the cars and the total number of cars rented of that make
Screenshot 2018-11-30 at 11.37.51 PM

A new car is bought and is updated to CAR, List the each make of the car and the number of that make
The new record of CAR:
Screenshot 2018-11-30 at 11.42.08 PM

Now count the number of cars of each make:
Screenshot 2018-11-30 at 11.43.25 PM

List the registration number and the make of the cars that have not been rented
Screenshot 2018-11-30 at 11.55.04 PM

Update Jones phone number to 988-2223
Before change:
Screenshot 2018-12-01 at 12.01.45 AM.png

The change statement:

Screenshot 2018-12-01 at 12.03.17 AM
after the change:
Screenshot 2018-12-01 at 12.04.25 AM

Posted in Database | Tagged | Leave a comment

[DATABASE] Difference between intersect and union

I have two relations namely PROPERTY and LEASE, see the PROPERTYNO attributes returned from PROPERTY and LEASE respectively:
Screenshot 2018-11-30 at 8.30.55 PM
Screenshot 2018-11-30 at 8.31.09 PM

In intersect, tuples which exists in both relations will be displayed removing duplicates.
Screenshot 2018-11-30 at 8.33.54 PM.png

In union, all tuples from property and all tuples from lease will be displayed removing duplicates:
Screenshot 2018-11-30 at 8.35.23 PM.png

Posted in Database | Tagged , | Leave a comment