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.

Features	RDBMS	Non-RDBMS
1.	They are structured in a very organized way. Example, phone book in which addresses and phone numbers are stored	They are distributed and document-oriented. Example, A file folders that has everything from phone number to address of a person, likes on facebook and preferences of online shopping
2.	SQL (Structured Query Language) is used to query and maintain the database	Data models are Flexible
3.	Data provided are stored in the tables	Types of databases for NoSql are: Key-Value Store, Column-based Store, Document-based Store-It, Graph-based
4.	Data are stored in the form of rows and columns	The Key-Value Store has Tables with Big Hash. Example Amazon S3, Riak.
5.	It provide a primary key facility to identify the row uniquely	Each block of the storage of Column-based Store data from only one of the column. Example, HBase, Cassandra
6.	For a quick retrieval of the data, indexes are created	The Document-based Store is a database that stores tagged elements type of documents
7.	Shares common column through primary and foreign key in more than two tables.	The Graph-based is a network database. Data are stored and represented using nodes and edges
8.	It ensures the compliancy of ACID( Atomicity, Consistency, Isolation, Durability)	Data are easily spread to multiple servers using storage and cloud computing. Development are rapid
9.	The data is unchanging and structured	It often have no structure or little structure and the data that are store contains large volumes.
10. Examples of available database in market	MySQL, Oracle, MA SQL Server, IMB DB2, Microsoft Azure, Sybase, MarlaDB, PostgreSQL	MongoDB,  Apache’s CouchDB, HBase, Oracle NoSQL, Apache’s Cassandra DB, Riak

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:}

Book	id	title	author	publisher	pubYear	isbnNo
	00267	Engineering Mathematics	W.C Evans	Oxford	2005	4478568

Book	Title	Author	Publisher
	An Introduction to Database Systems	A.K. Allen	McGrawHill
	Introduction to Relational Database	R. Torlone	Addison Wesley
	Programming with Visual Basic	N.B. Grag	Prentice Hall
	Mathematics for Engineering	T. Date	Addison Wesley
	Fundamental of Programming	W.L. Loure	Prentice Hall
	Engineering Mathematics	W.C. Evans	Oxford
	Engineering Mathematics	D. Woods	Prentice Hall

Result1	id	sName	address	state	contactNo	sex	email
	S10020	Nichole Tan	2-31-4, Jalan Mewah 6, Taman Merdeka	Penang	016-6752856	F	[email protected]
				Penang	017-2548695	M	[email protected]
				Penang	019-2574165	F	[email protected]

Result2	id	sName	address	state	contactNo	salary	joinedDate	dept	position	sex
	N574868	Winnie Tan Sin Yee	47-12, Country Heights, Perdana Villa	Penang	012-6581262	2100	1-Nov-05	HR	Secretary	F
	N778451	James Toh Heng Wai	33, Block A, Jalan Puteri 8	Penang		2000	20-Jun-05	ADM	Secretary	M
	A254766	Yong Ying Xiang	33, Block A, Jalan Puteri 8	Penang	013-5541338	2800	1-Mar-06	SE	Lecturer	F
	A740026	Vijay	Lot 3, Lebuh Timur, Kotamas	Penang	017-2548695	1800	15-Mar-07	SE	Tutor	M

FinalResult	sName
	Nichole Tan
	Vijay
	Fong Siow Ting
	Winnie Tan Sin Yee
	James Toh Heng Wai
	Yong Ying Xiang

Borrow	studentID	bookID
	10010	00025
	11094	00107
	11094	00005
	10121	00107
	10020	00001
	10020	00005

B	bookID
	00005
	00107

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.