Question

Question 3 (a) Identify the notation A B, and write a brief notes about it. [12 marks] (b) Do you believe that logical data independence and distribution independence are the backbone for distributed database Explain your position in detail. [13 marks) Question 4 (a) Study the table and show how you can create it from a scratch using MySQL or SQL Server DOB salary branch No staff No Name Name position Sex SL21 John White Manager M SG37 Ann Beach Assistant F SG14 David Ford Supervisor M SA9 Mary Howe Assistant F SGS Susan Brand Manager F SLAI Julic Lee Assistant F 1-Oct-45 30000.00 BOOS 10-Nov-60 12000.00 B003 24-Mar-58 18000.00 BOOB 19-Feb-70 9000.00 BOOT 3.Jun-40 24000.00 B003 13-Jun-65 9000.00 B005 117 marks (b)Explain how you can create this Table from the Table in (a) above staf No SL21 SG37 SG14 SA9 SGS SL41 Name John Ann David iName White Beech Ford Howe Brand Lee salary 2000.00 800.00 1200.00 600.00 1600.00 600.00 Mary Susan Julie 18 marks

Answer 1

3. (b) Yes,

explanation:

Distribution independence implies that users should not have to be aware of whether a database is distributed. And logical data independence implies that application programs and terminal activities remain logically unimpaired when information preserving changes of any kind that theoretically permit unimpaired are made to the base tables. So, they both are very important for a distributed database.

MySQL code:

4.(a)

create table A(
staffNo varchar(5),
fName varchar(10),
lName varchar(10),
position varchar(15),
sex char(1),
DOB date,
salary decimal(10, 2),
branchNo varchar(5)
);

insert into A values('SL21', 'John', 'White', 'Manager', 'M', '1945-10-01', 30000.00, 'B005');
insert into A values('SG37', 'Ann', 'Beech', 'Assistant', 'F', '1960-11-10', 12000.00, 'B003');
insert into A values('SG14', 'David', 'Ford', 'Supervisor', 'M', '1958-03-24', 18000.00, 'B003');
insert into A values('SA9', 'Mary', 'Howe', 'Assistant', 'F', '1970-02-19', 9000.00, 'B007');
insert into A values('SG5', 'Susan', 'Brand', 'Manager', 'F', '1940-06-03', 24000.00, 'B003');
insert into A values('SL41', 'Julie', 'Lee', 'Assistant', 'F', '1965-06-13', 9000.00, 'B006');

4. (b)

CREATE TABLE B AS
SELECT staffNo, fName, lName, salary
FROM A;

select * from B;