PCDE Design and implement database schemas — All Questions With Answers

Refer to the exhibit.
gcloud spanner databases ddl update my-database --ddl='CREATE TABLE Customers (CustomerId INT64 NOT NULL, Name STRING(100), Email STRING(100)) PRIMARY KEY (CustomerId); CREATE TABLE Orders (OrderId INT64 NOT NULL, CustomerId INT64 NOT NULL, OrderDate DATE, Amount FLOAT64) PRIMARY KEY (OrderId, CustomerId), INTERLEAVE IN PARENT Customers ON DELETE CASCADE;'

Refer to the exhibit.
{
  "bindings": [
    {
      "role": "roles/bigquery.dataOwner",
      "members": [
        "user:analyst@example.com"
      ]
    },
    {
      "role": "roles/bigquery.dataViewer",
      "members": [
        "group:analysts@example.com"
      ]
    }
  ]
}

Refer to the exhibit.
ERROR: (gcloud.bigquery.query) Error executing query: Query error: Query exceeded resource limits for tier 1. For details, see BigQuery tier 1 resource limits: https://cloud.google.com/bigquery/docs/tiers

CREATE TABLE Orders (
  OrderId INT64 NOT NULL,
  CustomerId INT64 NOT NULL,
  OrderDate DATE NOT NULL,
  TotalAmount FLOAT64 NOT NULL
) PRIMARY KEY (OrderId, CustomerId);

SELECT country, SUM(sales) 
FROM sales_data
WHERE date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY country
-- Query results: bytes processed: 500 GB, bytes billed: 500 GB, number of partitions processed: 31

rules_version = '2';
service cloud.firestore {
  match /databases/{database}/documents {
    match /users/{userId} {
      allow read, write: if request.auth.uid == userId;
    }
    match /messages/{messageId} {
      allow read: if resource.data.visibility == 'public' || request.auth.uid == resource.data.senderId;
      allow create: if request.auth.uid == resource.data.senderId;
      allow delete: if request.auth.uid == resource.data.senderId || request.auth.token.isAdmin == true;
    }
  }
}

Refer to the exhibit.
```
gcloud spanner databases ddl describe my-database --instance=test-instance
---
CREATE TABLE Sensors (
  SensorId INT64 NOT NULL,
  Location STRING(100),
) PRIMARY KEY (SensorId);

CREATE TABLE Readings (
  SensorId INT64 NOT NULL,
  Timestamp TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
  Value FLOAT64,
) PRIMARY KEY (SensorId, Timestamp DESC),
  INTERLEAVE IN PARENT Sensors ON DELETE CASCADE;
```

Refer to the exhibit.
```json
{
  "schema": {
    "fields": [
      {
        "name": "order_id",
        "type": "INTEGER"
      },
      {
        "name": "customer",
        "type": "STRING"
      },
      {
        "name": "items",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
          {"name": "product", "type": "STRING"},
          {"name": "quantity", "type": "INTEGER"},
          {"name": "price", "type": "FLOAT"}
        ]
      }
    ]
  }
}
```

Refer to the exhibit.

-- Cloud Spanner DDL statement
CREATE TABLE Users (
  UserId INT64 NOT NULL,
  Name STRING(100) NOT NULL,
  Email STRING(100) NOT NULL
) PRIMARY KEY (UserId);

CREATE TABLE Orders (
  OrderId INT64 NOT NULL,
  UserId INT64 NOT NULL,
  OrderDate DATE NOT NULL,
  Amount FLOAT64 NOT NULL,
  CONSTRAINT FK_User FOREIGN KEY (UserId) REFERENCES Users (UserId)
) PRIMARY KEY (OrderId);

Refer to the exhibit.

gcloud spanner instances describe test-instance
-- output:
config: nam3
nodeCount: 2
processingUnits: 2000
state: READY

-- Schema:
CREATE TABLE Events (
  EventId INT64 NOT NULL,
  EventType STRING(50) NOT NULL,
  EventData BYTES(MAX) NOT NULL,
  CreatedAt TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (EventId);

CREATE TABLE Singers (
  SingerId INT64 NOT NULL,
  FirstName STRING(1024),
  LastName STRING(1024),
  SingerInfo BYTES(MAX)
) PRIMARY KEY (SingerId);

CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  AlbumName STRING(1024)
) PRIMARY KEY (SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;