Observe the following tables VIDEO and MEMBER carefully and write the name of the RDBMS operation out of (i) SELECTION (ii) PROJECTION (iii) UNION (iv) CARTESIAN PRODUCT, which has been used to produce the output as shown below. Also, find the
Degree and Cardinality of the final result.
TABLE: VlDEO
VNO | VNAME | TYPE |
F101 | The Last Battle | Fiction |
C101 | Angels and Devils | Comedy |
A102 | Daredevils | Adventure |
TABLE: MEMBER
MNO | MNAME |
M101 | Namish Gupta |
M102 | Sana Sheikh |
M103 | Lara James |
TABLE: FINAL RESULT
VNO | VNAME | TYPE | MNO | MNAME |
F101 | The Last Battle | Fiction | M101 | Namish Gupta |
F101 | The Last Battle | Fiction | M102 | Sana Sheikh |
F101 | The Last Battle | Fiction | M103 | Lara James |
C101 | Angels and Devils | Comedy | M101 | Namish Gupta |
C101 | Angels and Devils | Comedy | M102 | Sana Sheikh |
C101 | Angels and Devils | Comedy | M103 | Lara James |
A102 | Daredevils | Adventure | M101 | Namish Gupta |
A102 | Daredevils | Adventure | M102 | Sana Sheikh |
A102 | Daredevils | Adventure | M103 | Lara James |
Concept: Relational Data Model
What kind of data gets stored in cookies and how is it useful ?
Concept: Internet
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii) which are based on the table.
TABLE: Account
ANO | ANAME | ADDRESS |
101 | Nirja Singh | Bangalore |
102 | Rohan Gupta | Chennai |
103 | Ali Reza | Hyderabad |
104 | Rishabh Jain | Chennai |
105 | Simran Kaur | Chandigarh |
TABLE: TRANSACT
TRNO | ANO | AMOUNT | TYPE | DOT |
T001 | 101 | 2500 | Withdraw | 2017-12-21 |
T002 | 103 | 3000 | Deposit | 2017-06-01 |
T003 | 102 | 2000 | Withdraw | 2017-0S-12 |
T004 | 103 | 1000 | Deposit | 2017-10-22 |
T005 | 101 | 12000 | Deposit | 2017-11-06 |
1) To display details of all transaction of TYPE Depos1t from Table TRANSACT.
2) To display the ANO and AMOUNT of all Depos1ts and Withdrawals done in the month of October 2017 from table TRANSACT.
3) To display the last date of the transaction (DOT) from the table TRANSACT for the Accounts having ANO as 103
4) To display all ANO, ANAME and DOT of those persons from tables ACCOUNT and TRANSACT who have done transactions less than or equal to 3000?
5) SELECT ANO, ANAME FROM ACCOUNT WHERE ADDRESS NOT IN ('CHENNAI', 'BANGALORE');
6) SELECT DISTINCT ANO FROM TRANSACT
7) SELECT ANO, COUNT(*), MIN(AMOUNT) FROM TRANSACT GROUP BY AND HAVING COUNT(*) > 1;
8) SELECT COUNT(*), SUM(AMOUNT) FROM TRANSACT WHERE DOT <= '2017-06-01'
Concept: Structured Query Language Advantages of Using SQL
State any one Absorption Law of Boolean Algebra and verily it using truth table.
Concept: Truth Tables (Boolean Algebra)
Observe the following table MEMBER carefully and write the name of the RDBMS operation out of (i) SELECTION (ii) PROJECTION (iii) UNION (iv) CARTESIAN PRODUCT, which has been used to produce the output as shown in RESULT. Also, find the Degree and Cardinality of the RESULT :
MEMBER
NO | MNAME | STREAM |
M001 | JAYA | SCIENCE |
M002 | ADITYA | HUMANITIES |
M003 | HANSRAJ | SCIENCE |
M004 | SHIVAK | COMMERCE |
RESULT
NO | MNAME | STREAM |
M002 | ADI'l'YA | HUMANITIES |
Concept: Structured Query Language Advantages of Using SQL
Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables.
DVD
DCODE | DTITLE | DTYPE |
F101 | Henry Martin | Folk |
C102 | Dhrupad | Classical |
C101 | The Planets | Classical |
F102 | Universal Soldier | Folk |
R102 | A day in life | Rock |
MEMBER
MID | NAME | DCODE | ISSUEDATE |
101 | AGAM SINGH | R102 | 2017-11-30 |
103 | ARTH JOSEPH | F102 | 2016-12-13 |
102 | NISHA HANS | C101 | 2017-07- 24 |
1) To display all details from the table MEMBER in descending order of ISSUEDATE
2) To display the DCODE and DTITLE of all Folk Type DVDs from the table DVD.
3) To display the DTYPE and number of DVDs in each DTYPE from the table DVD.
4) To display all NAME and ISSUEDATE of those members from the table. MEMBER who have DVDs issued (i.e., ISSUEDATE) in the year 2017
5) SELECT MIN(ISSUEDATE) FROM MEMBER
6) SELECT DISTINCT DTYPE FROM DVD;
7) SELECT D.DCODE, NAME, DTITLE FROM DVD D, MEMBER M WHERE D.DCODE=M.DCODE;
8) SELECT DTITLE FROM DVD WHERE DTYPE NOT IN ("Folk", "Classical" ) ;
Concept: Structured Query Language Advantages of Using SQL
Consider the following DEPT and EMPLOYEE tables. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii).
Table: DEPT
DCODE | DEPARTMENT | LOCATION |
D01 | INFRASTRUCTURE | DELHI |
D02 | MARKETING | DELHI |
D03 | MEDIA | MUMBAI |
D04 | FINANCE | KOLKATA |
D05 | HUMAN RESOURCE | MUMBAI |
Table: EMPLOYEE
ENO | NAME | DOJ | DOB | GENDER | DCODE |
1001 | George K | 2013-09-02 | 1991-09-01 | MALE | D01 |
1002 | Ryma Sen | 2012-12-11 | 1990-12-15 | FEMALE | D03 |
1003 | Mohitesh | 2013-02-03 | 1987-09--04 | MALE | D05 |
1007 | Anil Jha | 2014-01-17 | 1984-10-19 | MALE | D04 |
1004 | Manila Sahai | 2012-12-09 | 1986-11-14 | FEMALE | D01 |
1005 | R SAHAY | 2013-11-18 | 1987-03-31 | MALE | D02 |
1006 | Jaya Priya | 2014-06-09 | 1985-06-23 | FEMALE | D05 |
Note: DOJ refers to the date of joining ·and DOB refers to the date of Birth of employees
1) To display Eno, Name, Gender from the table EMPLOYEE in ascending order of Eno.
2) To display the Name of all the MALE Employees from the table EMPLOYEE.
3) To display the Eno and Name of those employees from the table EMPLOYEE who are born between '1987-01-01' and '1991-12-01'.
4) To count and display FEMALE employees who have Joined after '1986-01-01'.
5) SELECT COUNT(*), DCODE FROM EMPLOYEE GROUP BY DCODE HAVING COUNT(*)>1;
6) SELECT DISTINCT DEPARTMENT FROM DEPT;
7) SELECT NAME, DEPARTMENT FROM EMPLOYEE E, DEPT D WHERE E.DCODE=D.DCODE AND EN0<1003;
8) SELECT MAX(DOJ), MIN(DOB) FROM EMPLOYEE;
Concept: Structured Query Language Advantages of Using SQL
Verify the following using Boolean Laws : U'+V = U'V'+ U' .V + U.V
Concept: Commutative Law
Differentiate between communication using Optical Fiber and Ethernet Cable in context of the wired medium of communication technologies.
Concept: Optical Fiber
State DeMorgan's Laws of Boolean Algebra and verify them using a truth table.
Concept: DeMorgan’S Law/Theorem and Their Applications
Hi-Standard Tech Traj.ning Ltd. is a Mumbai based organization which is expanding its office set-up to Chennai. At Chennai office compound, they are planning to have 3 different blocks for Admin, Training and Accounts related activities. Each block has a number of computers, which are required to be connected in a network for communication, data and resource sharing.
As a network consultant, you have to suggest the best network related solutions for them for issues/problems raised by them in (i) to (iv), as per the distances between various blocks/locations and other given parameters.
Shortest differences between various blocks/locations:
Admin Block to Accounts Block | 300 Metres |
Accounts Block to Training Block | 150 Metres |
Admin Block to Training Block | 200 Metres |
MUMBAI Head Office to CHENNAI Office | 1300 Km |
The number of computers installed at various blocks is as follows:
Training Block | 150 |
Accounts Block | 30 |
Admin Block | 40 |
1) Suggest the most appropriate block/location to house the SERVER in the CHENNAI office (out of the 3 blocks) to get the best and effective connectivity. Justify your answer.
2) Suggest the best-wired medium and draw the cable layout (Block to Block) to efficiently connect various blocks within the CHENNAI office compound.
3) Suggest a device/software and its placement that would provide data security for the entire network of the CHENNAI office.
4) Suggest a device and the protocol that shall be needed to provide wireless Internet access to all smartphone/laptop users in the CHENNAI office
Concept: Network Devices - Modem, RJ45 Connector, Ethernet Card, Router, Switch, Gateway, Wifi Card
Differentiate between packet switching over message switching
Concept: Network Devices - Modem, RJ45 Connector, Ethernet Card, Router, Switch, Gateway, Wifi Card
Derive a Canonical POS expression for a Boolean function FN, represented by the following truth truth table:
X | y | z | FN (X, Y, Z) |
0 | 0 | 0 | 1 |
0 | 0 | 1 | 1 |
0 | 1 | 0 | 0 |
0 | 1 | 1 | 0 |
1 | 0 | 0 | 1 |
1 | 0 | 1 | 0 |
1 | 1 | 0 | 0 |
1 | 1 | 1 | 1 |
Concept: Obtaining Sum of Product (SOP) and Product of Sum (POS) Form the Truth Table
Differentiate bel ween Bus Topology and Star Topology of Networks
Concept: Network Topologies and Types - Bus, Star, Tree, PAN, LAN, WAN, MAN
What are the advantages and disadvantages of Star Topology over Bus Topology
Concept: Network Topologies and Types - Bus, Star, Tree, PAN, LAN, WAN, MAN
CASE STUDY BASED QUESTION
Ayurveda Training Educational Institute is setting up its centre in Hyderabad with four specialised departments for Orthopedics, Neurology and Pediatrics along with an administrative office in separate buildings. The physical distance between these department buildings and the number of computers to be installed in these departments and administrative office are given as follows.
Shortest distance between various locations in meters:
Administrative Office to Orthopedics Unit | 55 |
Neurology Unit to Administrative Office | 30 |
Orthopedics Units to Neurology Unit | 70 |
Pediatrics Unit to Neurology Unit | 50 |
Pediatrics Unit to Administrative Office | 40 |
Pediatrics Unit to Orthopedics Unit | 110 |
Number of Computers installed at various locations are as follows
Pediatrics Unit | 40 |
Administrative Office | 140 |
Neurology | 50 |
Orthopedics Unit | 80 |
1) Suggest the most suitable location to install the main server of this institution to get efficient connectivity.
2) Suggest the best cable layout for effective network connectivity of the building having server with all the other buildings.
3) Suggest the devices to be installed in each of these building for connecting computers installed within the building out of the following
- Gateway
- Modem
- Switch
4) Suggest the topology of the network and network cable for efficiently connecting each computer installed in each of the building out of the following:
Topologies: Bus Topology, Star Topology
Network Cable: Single Pair Telephone Cable, Coaxial Cable, Ethernet Cable
Concept: Network Topologies and Types - Bus, Star, Tree, PAN, LAN, WAN, MAN
Derive a Canonical POS expression for a Boolean function G, represented by the following truth table:
X | Y | Z | G(X, Y, Z) |
0 | 0 | 0 | 0 |
0 | 0 | 1 | 0 |
0 | 1 | 0 | 1 |
0 | 1 | 1 | 0 |
1 | 0 | 0 | 1 |
1 | 0 | 1 | 1 |
1 | 1 | 0 | 0 |
1 | 1 | 1 | 1 |
Concept: Obtaining Sum of Product (SOP) and Product of Sum (POS) Form the Truth Table
Derive a Canonical POS expression for a Boolean function F, represented by the following truth table
P | Q | R | F(P,Q,R) |
0 | 0 | 0 | 1 |
0 | 0 | 1 | 0 |
0 | 1 | 0 | 0 |
0 | 1 | 1 | 1 |
1 | 0 | 0 | 1 |
1 | 0 | 1 | 0 |
1 | 1 | 0 | 0 |
1 | 1 | 1 | 1 |
Concept: Obtaining Sum of Product (SOP) and Product of Sum (POS) Form the Truth Table
illustrate the layout for connecting 5 computers in a Bus topology of Networks
Concept: Network Topologies and Types - Bus, Star, Tree, PAN, LAN, WAN, MAN
illustrate the layout for connecting 5 computers in a star topology of Networks
Concept: Network Topologies and Types - Bus, Star, Tree, PAN, LAN, WAN, MAN