Create scripts of SQL statements for the following situations

zipped_to_include_schema.zip

Home >Information Systems homework help >Create scripts of SQL statements for the following situations

ABC_Name.mwb

document.mwb.xml

{63088D8A-3284-4CAD-AB82-86A16BC617EE} 0 1 0 1 -1 -1 -1 com.mysql.rdbms.mysql.datatype.int clientID {F6AF218E-B5E6-4834-A709-AC26A7BAC5C9} 0 0 0 45 -1 -1 com.mysql.rdbms.mysql.datatype.varchar firstName {F6AF218E-B5E6-4834-A709-AC26A7BAC5C9} 0 0 0 45 -1 -1 com.mysql.rdbms.mysql.datatype.varchar lastName {F6AF218E-B5E6-4834-A709-AC26A7BAC5C9} 0 0 0 45 -1 -1 com.mysql.rdbms.mysql.datatype.varchar address {F6AF218E-B5E6-4834-A709-AC26A7BAC5C9} 0 0 0 {BA5D2698-6A47-46C9-A322-72099E323171} {7D0502EB-C416-4202-B7A6-FD6161D6FC43} 0 0 PRIMARY 1 PRIMARY 0 PRIMARY {F6AF218E-B5E6-4834-A709-AC26A7BAC5C9} 0 {7D0502EB-C416-4202-B7A6-FD6161D6FC43} 0 InnoDB 0 0 0 0 2015-10-08 23:22 2015-10-08 23:28 0 client {05B12C37-27C6-41D7-B41F-37CC331C266D} 0 1 0 1 -1 -1 -1 com.mysql.rdbms.mysql.datatype.int clientContactID {85FEE46C-5DEE-4B2A-90C4-C9D42A620819} 0 0 0 45 -1 -1 com.mysql.rdbms.mysql.datatype.varchar firstName {85FEE46C-5DEE-4B2A-90C4-C9D42A620819} 0 0 0 45 -1 -1 com.mysql.rdbms.mysql.datatype.varchar lastName {85FEE46C-5DEE-4B2A-90C4-C9D42A620819} 0 0 0 45 -1 -1 com.mysql.rdbms.mysql.datatype.varchar address {85FEE46C-5DEE-4B2A-90C4-C9D42A620819} 0 {F6AF218E-B5E6-4834-A709-AC26A7BAC5C9} 0 NO ACTION 1 1 0 {85FEE46C-5DEE-4B2A-90C4-C9D42A620819} 1 NO ACTION firstName 0 0 {EC5FCD8B-659D-480A-B4C2-5FFBAE098232} {60586214-8239-40F2-90AA-7C527D03E636} 0 0 PRIMARY 1 PRIMARY 0 PRIMARY {85FEE46C-5DEE-4B2A-90C4-C9D42A620819} 0 {60586214-8239-40F2-90AA-7C527D03E636} 0 InnoDB 0 0 0 0 2015-10-08 23:24 2015-10-08 23:35 0 clientContact {05B12C37-27C6-41D7-B41F-37CC331C266D} 0 1 0 1 -1 -1 -1 com.mysql.rdbms.mysql.datatype.int salesPersonID {9A41B501-A016-4AA4-8194-B7307B620775} 0 0 0 45 -1 -1 com.mysql.rdbms.mysql.datatype.varchar contactDate {9A41B501-A016-4AA4-8194-B7307B620775} 0 0 0 45 -1 -1 com.mysql.rdbms.mysql.datatype.varchar clientID {9A41B501-A016-4AA4-8194-B7307B620775} 0 0 0 45 -1 -1 com.mysql.rdbms.mysql.datatype.varchar conversation {9A41B501-A016-4AA4-8194-B7307B620775} 0 0 1 -1 -1 -1 com.mysql.rdbms.mysql.datatype.int client_clientID client_clientID {9A41B501-A016-4AA4-8194-B7307B620775} 0 {F6AF218E-B5E6-4834-A709-AC26A7BAC5C9} {7C6B23EA-4164-4F33-965F-A01DB8372992} 0 NO ACTION {3FD06B8B-E37B-4CC0-97B4-78C1012629C8} 1 1 0 {9A41B501-A016-4AA4-8194-B7307B620775} {BA5D2698-6A47-46C9-A322-72099E323171} 1 NO ACTION fk_salesPerson_client fk_salesPerson_client 0 0 {0EC9A063-A3F4-460C-AC74-605E3D68F173} {AA905F79-1108-4973-A1AA-B3E4911666D5} 0 0 {7C6B23EA-4164-4F33-965F-A01DB8372992} {AA905F79-1108-4973-A1AA-B3E4911666D5} 0 0 PRIMARY 1 PRIMARY 0 PRIMARY {9A41B501-A016-4AA4-8194-B7307B620775} 0 0 {7C6B23EA-4164-4F33-965F-A01DB8372992} {3FD06B8B-E37B-4CC0-97B4-78C1012629C8} 0 0 INDEX 0 fk_salesPerson_client_idx 0 fk_salesPerson_client {9A41B501-A016-4AA4-8194-B7307B620775} 0 {AA905F79-1108-4973-A1AA-B3E4911666D5} 0 InnoDB 0 0 0 0 2015-10-08 23:25 2015-10-08 23:28 0 salesPerson {05B12C37-27C6-41D7-B41F-37CC331C266D} 0 1 0 1 -1 -1 -1 com.mysql.rdbms.mysql.datatype.int addressID {DE23A9EA-A8DF-462C-A9FA-5FFD1ACE347B} 0 0 0 45 -1 -1 com.mysql.rdbms.mysql.datatype.varchar clientID {DE23A9EA-A8DF-462C-A9FA-5FFD1ACE347B} 0 0 0 45 -1 -1 com.mysql.rdbms.mysql.datatype.varchar emailInfo {DE23A9EA-A8DF-462C-A9FA-5FFD1ACE347B} 0 0 0 45 -1 -1 com.mysql.rdbms.mysql.datatype.varchar region {DE23A9EA-A8DF-462C-A9FA-5FFD1ACE347B} 0 {F6AF218E-B5E6-4834-A709-AC26A7BAC5C9} 0 NO ACTION 1 1 0 {DE23A9EA-A8DF-462C-A9FA-5FFD1ACE347B} 1 NO ACTION clientID 0 0 {8FE091DE-EE17-4328-A77F-ED7EEEB76D3D} {63079D81-2835-42EB-87C4-4ED14007B39A} 0 0 PRIMARY 1 PRIMARY 0 PRIMARY {DE23A9EA-A8DF-462C-A9FA-5FFD1ACE347B} 0 {63079D81-2835-42EB-87C4-4ED14007B39A} 0 InnoDB 0 0 0 0 2015-10-08 23:26 2015-10-08 23:32 0 address {05B12C37-27C6-41D7-B41F-37CC331C266D} utf8 utf8_general_ci 0 0 mydb {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.charset.big5 com.mysql.rdbms.mysql.charset.dec8 com.mysql.rdbms.mysql.charset.cp850 com.mysql.rdbms.mysql.charset.hp8 com.mysql.rdbms.mysql.charset.koi8r com.mysql.rdbms.mysql.charset.latin1 com.mysql.rdbms.mysql.charset.latin2 com.mysql.rdbms.mysql.charset.swe7 com.mysql.rdbms.mysql.charset.ascii com.mysql.rdbms.mysql.charset.ujis com.mysql.rdbms.mysql.charset.sjis com.mysql.rdbms.mysql.charset.hebrew com.mysql.rdbms.mysql.charset.tis620 com.mysql.rdbms.mysql.charset.euckr com.mysql.rdbms.mysql.charset.koi8u com.mysql.rdbms.mysql.charset.gb2312 com.mysql.rdbms.mysql.charset.greek com.mysql.rdbms.mysql.charset.cp1250 com.mysql.rdbms.mysql.charset.gbk com.mysql.rdbms.mysql.charset.latin5 com.mysql.rdbms.mysql.charset.asmscii8 com.mysql.rdbms.mysql.charset.utf8 com.mysql.rdbms.mysql.charset.ucs2 com.mysql.rdbms.mysql.charset.cp866 com.mysql.rdbms.mysql.charset.keybcs2 com.mysql.rdbms.mysql.charset.macce com.mysql.rdbms.mysql.charset.macroman com.mysql.rdbms.mysql.charset.cp852 com.mysql.rdbms.mysql.charset.latin7 com.mysql.rdbms.mysql.charset.cp1251 com.mysql.rdbms.mysql.charset.cp1256 com.mysql.rdbms.mysql.charset.cp1257 com.mysql.rdbms.mysql.charset.binary com.mysql.rdbms.mysql.charset.geostd8 com.mysql.rdbms.mysql.charset.cp932 com.mysql.rdbms.mysql.charset.eucjpms com.mysql.rdbms.mysql.charset.utf8mb4 com.mysql.rdbms.mysql.charset.utf16 com.mysql.rdbms.mysql.charset.utf32 {05B12C37-27C6-41D7-B41F-37CC331C266D} **.* SCHEMA ALL {E600BCA9-6730-44B7-A69F-A23169305CB7} 0 0 owner {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} **.* TABLE SELECT {63717946-4F85-49EB-B6BF-925C1DDA3E59} 0 0 table.readonly {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} **.* TABLE SELECT INSERT TRIGGER {D2D9B2C0-3734-4120-A6D9-5833CB732411} 0 0 table.insert {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} **.* TABLE SELECT INSERT TRIGGER UPDATE DELETE {BDA4EA2E-1FBC-4403-B5FD-68EE04A32BE7} 0 0 table.modify {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} **.* ROUTINE EXECUTE {D6FB0B79-5925-4F96-BFF8-05CCEDF7D850} 0 0 routine.execute {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.tinyint com.mysql.rdbms.mysql.datatype.smallint com.mysql.rdbms.mysql.datatype.mediumint com.mysql.rdbms.mysql.datatype.int com.mysql.rdbms.mysql.datatype.bigint com.mysql.rdbms.mysql.datatype.float com.mysql.rdbms.mysql.datatype.real com.mysql.rdbms.mysql.datatype.double com.mysql.rdbms.mysql.datatype.decimal com.mysql.rdbms.mysql.datatype.char com.mysql.rdbms.mysql.datatype.nchar com.mysql.rdbms.mysql.datatype.varchar com.mysql.rdbms.mysql.datatype.nvarchar com.mysql.rdbms.mysql.datatype.binary com.mysql.rdbms.mysql.datatype.varbinary com.mysql.rdbms.mysql.datatype.tinytext com.mysql.rdbms.mysql.datatype.text com.mysql.rdbms.mysql.datatype.mediumtext com.mysql.rdbms.mysql.datatype.longtext com.mysql.rdbms.mysql.datatype.tinyblob com.mysql.rdbms.mysql.datatype.blob com.mysql.rdbms.mysql.datatype.mediumblob com.mysql.rdbms.mysql.datatype.longblob com.mysql.rdbms.mysql.datatype.datetime com.mysql.rdbms.mysql.datatype.datetime_f com.mysql.rdbms.mysql.datatype.date com.mysql.rdbms.mysql.datatype.time com.mysql.rdbms.mysql.datatype.time_f com.mysql.rdbms.mysql.datatype.year com.mysql.rdbms.mysql.datatype.timestamp com.mysql.rdbms.mysql.datatype.timestamp_f com.mysql.rdbms.mysql.datatype.geometry com.mysql.rdbms.mysql.datatype.point com.mysql.rdbms.mysql.datatype.linestring com.mysql.rdbms.mysql.datatype.polygon com.mysql.rdbms.mysql.datatype.geometrycollection com.mysql.rdbms.mysql.datatype.multipoint com.mysql.rdbms.mysql.datatype.multilinestring com.mysql.rdbms.mysql.datatype.multipolygon com.mysql.rdbms.mysql.datatype.bit com.mysql.rdbms.mysql.datatype.enum com.mysql.rdbms.mysql.datatype.set com.mysql.rdbms.mysql.datatype.tinyint TINYINT(1) BOOL {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.tinyint TINYINT(1) BOOLEAN {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.decimal DECIMAL(10,0) FIXED {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.float FLOAT FLOAT4 {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.double DOUBLE FLOAT8 {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.tinyint TINYINT(4) INT1 {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.smallint SMALLINT(6) INT2 {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.mediumint MEDIUMINT(9) INT3 {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.int INT(11) INT4 {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.bigint BIGINT(20) INT8 {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.int INT(11) INTEGER {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.mediumblob MEDIUMBLOB LONG VARBINARY {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.mediumtext MEDIUMTEXT LONG VARCHAR {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.mediumtext MEDIUMTEXT LONG {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.mediumint MEDIUMINT(9) MIDDLEINT {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.decimal DECIMAL(10,0) NUMERIC {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.decimal DECIMAL(10,0) DEC {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} com.mysql.rdbms.mysql.datatype.char CHAR(1) CHARACTER {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} -1 5 6 -1 0 Version {EF9D0CED-17EB-4C19-821F-1FE385A7E2B9} default {9270F099-7FAB-40D4-BA29-F62F56F405F0} crowsfoot 0 fk_salesPerson_client 0.e+000 0.e+000 0.e+000 0.e+000 0.e+000 0.e+000 {E23CA0EB-5E86-4C35-8653-773F099CE4A5} 0.e+000 0.e+000 0.e+000 0 {9419C636-9471-4B6B-9DB4-0890D5D97432} {1C3B7621-DB9B-4F84-A136-5B68B7041AE3} {65E83056-7DC8-4F04-BC73-1063462941F3} 1 clientID 0.e+000 0.e+000 0.e+000 0.e+000 0.e+000 0.e+000 {1161536F-0045-4B52-96C2-593B7F79BD68} 0.e+000 0.e+000 0.e+000 0 {9419C636-9471-4B6B-9DB4-0890D5D97432} {D4057D9C-D732-4003-BA6A-FDB3F04A1FEC} {65E83056-7DC8-4F04-BC73-1063462941F3} 1 firstName 0.e+000 0.e+000 0.e+000 0.e+000 0.e+000 0.e+000 {55E40B21-5BB4-48F8-B160-FB6781E7D097} 0.e+000 0.e+000 0.e+000 0 {9419C636-9471-4B6B-9DB4-0890D5D97432} {6544A23B-6C42-47E8-82E2-91C657EA7028} {65E83056-7DC8-4F04-BC73-1063462941F3} 1 1 0 0 -1 {DE23A9EA-A8DF-462C-A9FA-5FFD1ACE347B} 0 #98BFDA 1 1.4e+002 {302A7FA2-F118-4857-8361-D37EFB29E5A2} 4.84e+002 0 0 1.46e+002 1.38e+002 {65E83056-7DC8-4F04-BC73-1063462941F3} 1 address 1 0 0 -1 {F6AF218E-B5E6-4834-A709-AC26A7BAC5C9} 0 #98BFDA 1 1.4e+002 {302A7FA2-F118-4857-8361-D37EFB29E5A2} 2.61e+002 0 0 1.46e+002 1.4e+002 {65E83056-7DC8-4F04-BC73-1063462941F3} 1 client 1 0 0 -1 {85FEE46C-5DEE-4B2A-90C4-C9D42A620819} 0 #98BFDA 1 1.4e+002 {302A7FA2-F118-4857-8361-D37EFB29E5A2} 2.61e+002 0 0 3.89e+002 1.4e+002 {65E83056-7DC8-4F04-BC73-1063462941F3} 1 clientContact 1 0 0 -1 {9A41B501-A016-4AA4-8194-B7307B620775} 0 #98BFDA 1 1.62e+002 {302A7FA2-F118-4857-8361-D37EFB29E5A2} 4.4e+001 0 0 1.35e+002 1.54e+002 {65E83056-7DC8-4F04-BC73-1063462941F3} 1 salesPerson 1.38095e+003 EER Diagram {9270F099-7FAB-40D4-BA29-F62F56F405F0} {D4057D9C-D732-4003-BA6A-FDB3F04A1FEC} {9419C636-9471-4B6B-9DB4-0890D5D97432} {6544A23B-6C42-47E8-82E2-91C657EA7028} {1C3B7621-DB9B-4F84-A136-5B68B7041AE3} 1.38095e+003 0.e+000 0.e+000 1.9720000000000002e+003 {65E83056-7DC8-4F04-BC73-1063462941F3} 1 0 1.9720000000000002e+003 0.e+000 0.e+000 1.e+000 workbench/default com.mysql.rdbms.mysql Business Rule {9270F099-7FAB-40D4-BA29-F62F56F405F0} {65E83056-7DC8-4F04-BC73-1063462941F3} {63088D8A-3284-4CAD-AB82-86A16BC617EE} mcs New Model 2015-10-08 23:37 2015-10-08 23:22 Name of the project 1.0 Properties {63088D8A-3284-4CAD-AB82-86A16BC617EE} 1.4460000000000001e+001 6.3499999999999996e+000 6.3499999999999996e+000 6.3499999999999996e+000 portrait com.mysql.wb.papertype.a4 5.e+000 {63088D8A-3284-4CAD-AB82-86A16BC617EE}

lock

3444

@db/data.db

MySQL%20Server%20and%20MySQL%20Workbench%20assignment.docx

RUNNING HEAD: MySQL and MySQL Workbench assignment

MySQL Server and MySQL Workbench assignment Student’s Name College Affiliation

With the help of MySQL workbench, the following schema show various entities and their attributes connected in an Entity Relationship diagram as shown below;

The following table describes the data used in the schema above:

Entity	Description	Attributes	Constraints	Relationship
Client	“Client” table/ attribute store all the data that relates to the potential customers served by a salesperson.	FirstName, lastName, clientID, address,	Primary Key; clientID Foreign Key: address	A client has only one salesperson, A client may have many addresses.
clientContact	“clientContact” table/ attribute describes all the data that is can be used by a salesperson to contact them.	clientContactID, FirstName, lastName, address	P.K: clientContactID, F.K: firstname, lastname, address	Client has one clientContact.
salesperson	This table or attribute describes data that relates to a specific salesperson and the proceeding conversation with the client.	salesPersonID, firstname, lastname, contactDate, conversation, clientID, salesPersonID	P.K: salesPersonID FK: clientID	A sales person can have several clients.
address	Apart from contact, the address of the client or customer is stored in the address table. This includes all their email addresses and zip/ postal addresses.	addressID, clientID, region, emailInfo	PK: addressID FK: clientID	A client has one address.

The attributes entities describe above will appear in the schema as tables and the attributes as columns.