NATASS
ER-Diagram
Trigger & Stored Procedure Requirements
Trigger to Modify Staff Name to upper case in Staff Table when a new name is inserted.
CREATE TRIGGER `Update_Name_in_Staff` AFTER INSERT ON `Staff`
Update Staff set FirstName = upper(FirstName) and LastName = upper(LastName);
Trigger to Modify Bill Amount where Bill Amount >= 3000.
CREATE TRIGGER Update_Bill AFTER INSERT ON Bill
BEGIN
UPDATE Bill
SET BillAmount = BillAmount – BillAmount * 15 / 100
WHERE BillAmount >= 3000;
END$$
Store Procedure to Insert Data in Staff Table
DROP PROCEDURE IF EXISTS `sp_staff_INSERT_byPK`
GO
CREATE PROCEDURE sp_staff_INSERT_byPK
(
IN p_staffID VARCHAR(7) ,
IN p_FirstName VARCHAR(10) ,
IN p_LasttName VARCHAR(10) ,
IN p_Address VARCHAR(30) ,
IN p_DOB DATETIME ,
IN p_Degree VARCHAR(30) ,
IN p_Position VARCHAR(30) ,
IN p_PublicADMrank VARCHAR(10) ,
IN p_RecruitDate DATETIME ,
IN p_Childno TINYINT(4) )
BEGIN
INSERT INTO staff
(
staffID ,
FirstName ,
LasttName ,
Address ,
DOB ,
Degree ,
Position ,
PublicADMrank ,
RecruitDate ,
Childno
)
VALUES
(
p_staffID ,
p_FirstName ,
p_LasttName ,
p_Address ,
p_DOB ,
p_Degree ,
p_Position ,
p_PublicADMrank ,
p_RecruitDate ,
p_Childno ) ;
END
Store Procedure to Delete Record in Staff Table
GO
DROP PROCEDURE IF EXISTS `sp_staff_DELETE_byPK`
GO
CREATE PROCEDURE sp_staff_DELETE_byPK
(
IN p_staffID VARCHAR(7)
)
BEGIN
DELETE FROM staff
WHERE staffed = p_staffID ;
END
GO
Comparison Reflection Requirements
MS-Access Tables Creation Scripts
Run the following SQL to generate 3 tables in MS-Access 2007
CREATE TABLE Bill
(
BillID text(5),
BillName varchar(30),
BillType varchar(30),
BillAmount Number,
DueDate date,
DateofPay date
);
CREATE TABLE NatAss
(
NatAssID text(5),
AdninID text(5),
FinanceID text(5),
LogisticID text(5)
);
CREATE TABLE Equipment
(
EquipmentID text(5),
EquipmentName varchar(30),
EquipmentType varchar(30),
PurcDate date,
Cost Number,
Location varchar(30)
);
MYSQL Tables Creation Scripts
CREATE TABLE `natass`.`Bill` (
`BillID` VARCHAR( 5 ) NOT NULL ,
`BillName` VARCHAR( 30 ) NOT NULL ,
`BillType` VARCHAR( 30 ) NOT NULL ,
`BillAmount` SMALLINT NOT NULL ,
`DueDate` DATE NOT NULL ,
`DateofPay` DATE NOT NULL
PRIMARY KEY ( `BillID` )
) ENGINE = InnoDB;
CREATE TABLE `natass`.`NatAss` (
`NatAssID` VARCHAR( 5 ) NOT NULL ,
`AdminID` VARCHAR( 5 ) NOT NULL ,
`FinanceID` VARCHAR( 5 ) NOT NULL ,
`LogisticID` VARCHAR( 5 ) NOT NULL ,
PRIMARY KEY ( `NatAssID` )
) ENGINE = InnoDB;
CREATE TABLE `natass`.`Equipment` (
`EquipmentID` VARCHAR( 5 ) NOT NULL ,
`EquipmentName` VARCHAR( 30 ) NOT NULL ,
`EquipmentType` VARCHAR( 30 ) NOT NULL ,
`PurcDate` DATE NOT NULL ,
`Cost` MEDIUMINT NOT NULL ,
`Location` VARCHAR( 30 ) NOT NULL ,
PRIMARY KEY ( `EquipmentID` )
) ENGINE = InnoDB;
MYSQL is a RDBMS & MS-Access is DBMS
In DBMS stored procedure & Trigger are not available.
Programmatically we have to manage Jobs of MYSQL in MS-Access.
Ex.
GO
DROP PROCEDURE IF EXISTS `sp_staff_DELETE_byPK`
GO
CREATE PROCEDURE sp_staff_DELETE_byPK
(
IN p_staffID VARCHAR(7)
)
BEGIN
DELETE FROM staff
WHERE staffed = p_staffID ;
END
GO
From program connection has to be made to the database and ‘delete’ query has to be run.
Data Administration Procedure Requirements
Database Backup & Recovery
It is important to back up databases so that user can recover data and be up and running again in case problems occur, such as system crashes, hardware failures, or users deleting data by mistake. Backups are also essential as a safeguard before upgrading a MySQL installation, and they can be used to transfer a MySQL installation to another system or to set up replication slave servers.
MySQL offers a variety of backup strategies from which you can choose the methods that best suit the requirements for your installation. This chapter discusses several backup and recovery topics with which you should be familiar:
- Types of backups: Logical versus physical, full versus incremental, and so forth
- Methods for creating backups
- Recovery methods, including point-in-time recovery
- Backup scheduling, compression, and encryption
- Table maintenance, to enable recovery of corrupt tables
For storage engines that represent each table using its own files, tables can be backed up by copying those files. For example, MyISAM tables are stored as files, so it is easy to do a backup by copying files (*.frm, *.MYD, and *.MYI files). To get a consistent backup, stop the server or lock and flush the relevant tables:
You need only a read lock; this enables other clients to continue to query the tables while you are making a copy of the files in the database directory. The FLUSH TABLES statement is needed to ensure that the all active index pages are written to disk before you start the backup.
You can also create a binary backup simply by copying all table files, as long as the server isn’t updating anything. The mysqlhotcopy script uses this method. (But note that table file copying methods do not work if your database contains InnoDB tables. mysqlhotcopy does not work for InnoDB tables because InnoDB does not necessarily store table contents in database directories. Also, even if the server is not actively updating data, InnoDB may still have modified data cached in memory and not flushed to disk.)
Making Delimited-Text File Backups
To create a text file containing a table’s data, you can use SELECT * INTO OUTFILE 'file_name' FROM tbl_name. The file is created on the MySQL server host, not the client host. For this statement, the output file cannot already exist because permitting files to be overwritten constitutes a security risk. This method works for any kind of data file, but saves only table data, not the table structure.
Another way to create text data files (along with files containing CREATE TABLE statements for the backed up tables) is to use mysqldump with the --tab option.
To reload a delimited-text data file, use LOAD DATA INFILE or mysqlimport.
LH69
But you can order it from our service and receive complete high-quality custom paper. Our service offers TECHNOLOGY essay sample that was written by professional writer. If you like one, you have an opportunity to buy a similar paper. Any of the academic papers will be written from scratch, according to all customers’ specifications, expectations and highest standards.”
