Translate the brokerage into an SQL schema. Use the necessary SQL machinery to express all constraints speci?ed in the E-R model.

We will start with the diagram in Figure 4.30. In translating this E-R diagram, we choose to combine IsHandledBy with Account, WorksIn with Broker,and Office with ManagedBy. The rationale for these combinations is that in each case the entity type is a key role in the corresponding relationship. The reason why ManagedBy is combined with Office rather than Broker (which also happens to be a key role in the ManagedBy relationship) is that it is more likely for an o?ce to have a manager than it is for a broker to be a manager. Therefore, combining Office with ManagedBy into one table is unlikely to lead to many null values being stored with the Office table. In contrast, combining ManagedBy with Broker is likely to cause a situation where the value of the attribute Manages of the table Broker would be mostly NULL.

```
CREATE TABLE Client (
Name CHAR(20) NOT NULL,
Id INTEGER,
WorkPhone# CHAR(10),
PRIMARY KEY (Id)
)

CREATE TABLE Account (
Account# INTEGER,
DateOpened DATE,
Status CHAR(1),
-- We choose to keep Office here rather than with HasAccount
Office CHAR(10) NOT NULL,
Broker INTEGER,
PRIMARY KEY (Account#),
FOREIGN KEY Office REFERENCES Office(Phone#)
ON UPDATE CASCADE
OD DELETE NO ACTION,
FOREIGN KEY Broker REFERENCES Broker(Id)
ON UPDATE CASCADE
ON DELETE SET NULL
)

CREATE TABLE Office (
Phone# CHAR(10),
Address CHAR(50) NOT NULL,
Manager INTEGER,
ManagedSince DATE,
PRIMARY KEY (Phone#),
UNIQUE (Address),
FOREIGN KEY Manager REFERENCES Broker(Id)
ON UPDATE CASCADE
ON DELETE SET NULL
)

CREATE TABLE HasAccount (
Client INTEGER NOT NULL,
Account INTEGER NOT NULL,
FOREIGN KEY Client REFERENCES Client(Id)
ON UPDATE CASCADE
ON DELETE NO ACTION,
FOREIGN KEY Account REFERENCES Account(Account#)
ON UPDATE CASCADE
ON DELETE CASCADE
)

CREATE TABLE Broker (
Id INTEGER,
Name CHAR(20) NOT NULL,
WorksIn CHAR(10) NOT NULL,
WorksSince DATE,
PRIMARY KEY (Id)
FOREIGN KEY WorksIn REFERENCES Office(Phone#)
ON UPDATE CASCADE
ON DELETE CASCADE
)

CREATE TABLE BrokerPhone (
Broker INTEGER NOT NULL,
PhoneExtension CHAR(10) NOT NULL,
FOREIGN KEY Broker REFERENCES Broker(Id)
ON UPDATE CASCADE
ON DELETE CASCADE
)
```

Note that there is a reason why we split o? the PhoneExtension attribute from the Broker relation. If we join this attribute to Broker,thenId would no longer be a candidate key, so one of the foreign key constraints in Accounts would not be
possible.
We still need to write a constraint to ensure that a client cannot have two separate accounts in the same o?ce. We will use an SQL assertion for that. This last constraint might be a little hard to write with the amount of knowledge of SQL that the students
have at this point, so this part can be used for extra credit.

```
CREATE ASSERTION OneAcctPerClientOffice
CHECK NOT EXISTS (
SELECT C.Id
FROM Client C,
Account A1, HasAccount H1,
Account A2, HasAccount H2
WHERE C.Id = H1.Client AND C.Id = H2.Client
AND H1.Account = A1.Account#
AND H2.Account = A2.Account#
AND A1.Office <> A2.Office
)
```

We now turn to the representation of the trading information. Because Transaction is a key role in Trade, it is convenient to combine the two. Thus, we end up with the following tables:

```
CREATE TABLE Transaction (
Id INTEGER,
Date DATE NOT NULL,
Time TIME NOT NULL,
PricePerShare DECIMAL(6) NOT NULL,
NumberOfShares INTEGER NOT NULL,
StockSymbol CHAR(5) NOT NULL,
Account INTEGER NOT NULL,
PRIMARY KEY (Id),
FOREIGN KEY StockSymbol REFERENCES Stock
ON UPDATE NO ACTION
ON DELETE NO ACTION,
FOREIGN KEY Account REFERENCES Account(Account#)
ON UPDATE CASCADE
ON DELETE NO ACTION
)

CREATE TABLE Position (
Account INTEGER,
StockSymbol CHAR(5),
Amount INTEGER NOT NULL,
PRIMARY KEY (Account, Stock),
FOREIGN KEY Account REFERENCES Account(Account#)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY StockSymbol REFERENCES Stock
ON UPDATE CASCADE
ON DELETE CASCADE
)

CREATE TABLE Stock (
StockSymbol CHAR(5),
CompanyName CHAR(50) NOT NULL,
CurrentPrice DECIMAL(6) NOT NULL,
PRIMARY KEY (StockSymbol)
)
```