Question

6. [5] Create the following table: CREATE TABLE customer ( cust_name VARCHAR(30) NOT NULL, address VARCHAR(60), UNIQUE (cust name, address)); A. Run the following inserts and explain why both work and how will you prevent it INSERT INTO customer VALUES ('Alex Doe', NULL); INSERT INTO customer VALUES ('Alex Doe', NULL); 7. [5] Modify the following table definition to ensure that all employees have a minimum wage of $10 CREATE TABLE employee ( empId INT PRIMARY KEY, empName VARCHAR(40) NOT NULL, payRrate NUMERIC(5,2) NOT NULL, ); 8. [5] When the constraint on this table will be checked? CREATE TABLE Customer ( customer_name VARCHAR(30), account number CHAR(10), PRIMARY KEY (customer_name, account number), CHECK (account number IN (SELECT account number FROM account)));

Database Management

Answer 1

6) We will get the output for both the insert commands because Mysql considers NULL values as distinct values.

• So put not null constraint for address column and then it will shows an error if we enter duplicate values
• Mysql checks the uniqueness by combining the values in two columns...if the values in two columms are same then it will return error showing duplicate entry

5 6 7 8 9 10 11 12 13 14 create table customer cust_name varchar(30) NOT NULL, address varchar(60)NOT NULL, unique (cust_name, address) ); insert into customer values("Alex Doe", "Mumbai"); insert into customer values ("Alex Doe", "Mumbai"); select * from customer; Run (Ctrl-Enter) utput Runtime error Input Comments ERROR 1062 (23000) at line 14: Duplicate entry 'Alex Doe-Mumbai' for key 'cust_name

7) By using Check constarint we ensure that all employees have minimum wage 10

1. ll Result $sqlite3 database. sdb <main.sql 3 Error: near line 7: CHECK constraint failed: employee Execute > Share main.sql STDIN create table employee empId int primary key, empName varchar(40) NOT NULL, 4 payRrate numeric(5,2) NOT NULL CHECK (payRrate >=10) 5 ); 6 7 insert into employee values(1,"sai",9); 8 9 select *from employee; 10

​​​​​​​8) when we enter the account_number in customer table it will check the constraint whether that account_number exists in account table or not