ICT503_S20230432_Tutorial-8 (1)

.docx

School

St. John's University *

*We aren’t endorsed by this school

Course

1011

Subject

Information Systems

Date

Jan 9, 2024

Type

docx

Pages

4

Uploaded by DeaconEnergy3908

Report
Advanced SQL 1. What type of integrity is enforced when a primary key is declared? When a primary key is declared in a database table, it enforces entity integrity (also known as entity integrity constraint). Entity integrity ensures that each row or record in the table is uniquely identifiable and that no two rows can have the same primary key value. This means that the primary key enforces uniqueness, and every row must have a non-null, unique value in the primary key column(s). 2. Explain why it might be more appropriate to declare an attribute that contains only digits as a character data type instead of a numeric data type. Using a character data type (e.g., VARCHAR) is more appropriate when an attribute contains only digits in the following situations: To preserve leading zeros. When the data may include non-numeric characters. When flexibility or compatibility with other systems is required. For sorting and searching patterns within the data. 3. What is a trigger, and what is its purpose? Give an example? A trigger is a database object in a relational database management system (RDBMS) that is associated with a specific table and is automatically executed when certain events occur, such as data modifications on that table. The primary purpose of triggers is to enforce business rules, maintain data integrity, and automate actions based on database events. Example: Create a trigger to update inventory when an order is placed CREATE TRIGGER UpdateInventory AFTER INSERT ON Orders FOR EACH ROW BEGIN UPDATE Inventory SET quantity = quantity - NEW.quantity WHERE product_id = NEW.product_id; END; 4. The ConstructCo database stores data for a consulting company that tracks all charges to projects. The charges are based on the hours each employee works on each project. The structure and contents of the ConstructCo database are shown in Figure 1.
c. Write the SQL code that will create only the table structure for a table named EMP_1. This table will be a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in the following table. Use EMP_NUM as the primary key. Note that the JOB_CODE is the FK to JOB so be certain to enforce referential integrity. Your code should also prevent null entries in EMP_LNAME and EMP_FNAME. Query: CREATE TABLE EMP_1 ( EMP_NUM CHAR(3) PRIMARY KEY, EMP_LNAME VARCHAR(15) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_INITIAL CHAR(1), EMP_HIREDATE DATE, JOB_CODE CHAR(3), FOREIGN KEY (JOB_CODE) REFERENCES JOB(JOB_CODE) );
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help