Understanding Relational Database Concepts: Tuples, Attributes, And Views

Tuples and Attributes in Relational Database Concepts

In the Student Relation of figure 1, tuple is a row. Here, it has 5 tuples with 6 values that is called as 6 tuples. In general, n- tuples has a list ordered of values n.  If t1 is refer to first tuple variable for the relation STUDENT, then t1 = <S10010, Chan Wai Yee, 56A, Jalan 2/14, Taman Midah, kuala Lampur, 012-3256780, F, [email protected]>, similarly for the other.  Tuples may appear in sorted or unsorted order. The relations of the order remain the same and give same information or meaning.

Attribute is the relation characteristic of a column. In figure 1, the relation of STUDENT consists of 7 attributes namely, id (the student ID), sName, Address, State, Contact Number, Sex, Email.

Similarities

The relation tuple are similar to the file records, and attributes are field that are analogues (McGinnes and Kapros 2015).

Difference

The model of relational database is logically related to tables that contain data.  Attribute is a column were particular kind of data are kept; tuple is a row that holds the particular event or entity of the relation data.

1. c) Rows, Columns and Tables:

In a relational database concept, tables have a rows and a column. In the table, each of the rows will represent the related values of the collection (Keith et al. 2018). The information of the object is hold in the database to represent in the table. In any specific table, data has one kind of objects called the entities. Table is actually a concept of the data and relation is the concept of relational model.

Now in a table each of the rows represents one object called the entity that is the collection of the related values. If the diagram of figure 1 is considered, it has the data of one customer in one of the row in Student table. Thus, row has considered as a database concept and tuple as the concept of relational model.

Additionally, in a table each of the columns holds some kind of data. Each of the data is describe by some column name. For example, in the student table there are sName, Address, State and many more. Thus, column has considered as a database concept and attribute as the concept of relational model.

1. d) View:

A View is a set of queries for virtual tables that holds data from one or more tables and stored in a database in the form of an object (Learning Views In SQL Server 2018). This has done for the security purpose that restricts the user to view certain columns and rows.  It is better explained in the below given example:

Advantages of Views

Independence of logical data: The database tables and application can be independent through view.

Data integrity: DBMS checks the data has accessed through view that ensures to meet the constraints of the specified integrity.

Consistency: In the database, an unchanged, consistent image can be present by view for the structured database.

Structural simplicity: the user can make sense of the personalized view of the structure of the database that has presented through a set of virtual tables.

Query Simplicity: Queries of multiple tables can be drawn into single table through view

Security: The user can access the database through an authorized set of views that contain some specific data.

1.   (a) DML codes to create:

Book:

CREATE TABLE `book` (

  `id` int(11) NOT NULL,

  `title` varchar(255) NOT NULL,

  `author` varchar(150) NOT NULL,

  `publisher` varchar(150) NOT NULL,

  `pubYear` year(4) NOT NULL,

  `isbnNo` int(11) NOT NULL,

PRIMARY KEY (id))

Staff:

CREATE TABLE `staff` (

  `id` int(11) NOT NULL,

  `sName` varchar(100) NOT NULL,

  `address` varchar(255) NOT NULL,

  `state` varchar(100) NOT NULL,

  `contactNo` bigint(20) NOT NULL,

  `salary` int(11) NOT NULL,

  `joinedDate` varchar(50) NOT NULL,

  `dept` varchar(10) NOT NULL,

  `position` varchar(30) NOT NULL,

  `sex` enum(‘M’,’F’) NOT NULL,

PRIMARY KEY (id))

Student:

CREATE TABLE `student` (

  `id` varchar(10) NOT NULL,

  `sName` varchar(150) NOT NULL,

  `Address` varchar(255) NOT NULL,

  `state` varchar(50) NOT NULL,

  `contactNo` varchar(15) NOT NULL,

  `sex` enum(‘M’,’F’) NOT NULL,

  `email` varchar(255) NOT NULL,

PRIMARY KEY (id)

)

Borrow:

CREATE TABLE `borrow` (

  `studentID` int(11) NOT NULL,

  `bookID` int(11) NOT NULL

)

(b) DML codes to insert:

Staff:

INSERT INTO `staff` (`id`, `sName`, `address`, `state`, `contactNo`, `salary`, `joinedDate`, `dept`, `position`, `sex`) VALUES

(‘A102585’, ‘Tan Chee Chong’, ‘288, Lorong Duta, Pupa Village’, ‘Selangor’, 192587461, 2500, ’15-Jan-06′, ‘SE’, ‘Lecturer’, ‘M’);

Book:

INSERT INTO `book` (`id`, `title`, `author`, `publisher`, `pubYear`, `isbnNo`) VALUES

(00001, ‘An introduction to Database Systems’, ‘A.K. Allen’, ‘McGrawHill ‘, 2001, 74895841);

Borrow:

INSERT INTO `borrow` (`studentID`, `bookID`) VALUES (‘10010’, 0025);

Student:

INSERT INTO `student` (`id`, `sName`, `Address`, `state`, `contactNo`, `sex`, `email`) VALUES

(‘S10010’, ‘Chan Wai Yee’, ’56A, Jalan 2/14,Taman Midah’, ‘Kuala Lumpur’, ‘012-3256780’, ‘F’, ‘[email protected]‘);

(c) Enhancement features of SQL integrity:

(i)  alter table borrow add constraint index_book_id foreign key (bookID) references book(id)

ALTER TABLE borrow ADD CONSTRAINT unique_book UNIQUE (bookID);

(ii)ALTER TABLE staffADD CHECK (salary>0);

(a)  i.   ? _COUNT id  (σ _{salary > 2000} ^(Staff))

1. π _{id, SName, contactNo}^(Student)

   iii. π _{book.title, book.publisher}^(Book)                     _{book.id = book.id}^(Borrow)

^{(b) Output:}

References

“Learning Views In SQL Server”. 2018. C-Sharpcorner.Com. https://www.c-sharpcorner.com/UploadFile/f0b2ed/views-in-sql-server/

Keith, Mike, Merrick Schincariol, and Massimo Nardone. “Introduction.” In Pro JPA 2 in Java EE 8, pp. 1-24. Apress, Berkeley, CA, 2018.

McGinnes, Simon, and Evangelos Kapros. “Conceptual independence: A design principle for the construction of adaptive information systems.” Information Systems 47 (2015): 33-50.